# Help with creating bins in powerpivot

• ### Question

• I used this formula to sum only certain values. =CALCULATE(SUM([Amount]),Table1[Status]="Paid")

When I use this formula for unique customers it gives me the amount that I want. However, now I want to count how many customers have values within certain ranges. How can I do that? I was thinking of adding a calculated column but I'm not sure how to create the total (based on status= Paid) for each customer and THEN create bins for those totals. Thanks.

Friday, July 8, 2016 1:03 AM

• Since WHEN was sparsely defined, I assumed a monthly interval.
By instances, I assumed customers.
Simply remove Cust from the row field if desired.
Other tweaks can be made once you learn the "M" programming language.
If you are already fluent in DAX, I recommend the "Banding" chapter in the book
"Microsoft Excel 2013, Building Data Models with PowerPivot"
by Alberto Ferrari and Marco Russo.

• Proposed as answer by Sunday, July 24, 2016 2:53 AM
• Marked as answer by Sunday, July 24, 2016 9:47 AM
Tuesday, July 12, 2016 9:33 PM

### All replies

• It would help to provide some information on what your model looks like. But assuming that you have a fact table (Table1 with at least columns [Amount] and [Status], and something like [CustomerID]) and a separate Customer table linked to Table1, and the formula you mention is a measure called [TotalAmountPaid], counting customers can be done with e.g.

NumberOfLargeCustomers:= COUNTROWS(FILTER(Customer;[TotalAmountPaid]>1000000))

To create bins, you could make a small helper table "Bin" with the bins like this:

and

CustomersByBin:= SUMX(Bin; COUNTROWS(FILTER(Customer;[TotalAmountPaid]>Bin[Lower] && [TotalAmountPaid]<=Bin[Upper])))

This measure splits the number of customers by bin when you use Bin[Bin] as a filter, and returns all customers when you don't filter on Bin.

Saturday, July 9, 2016 5:32 AM
• Hello. Thanks for your help.  I'm having trouble getting your formula to work. Are you sure it's typed correctly? What are the semi-colons for? I ended up using commas and it seems to work.

CustomersByBin:= SUMX(Bin; COUNTROWS(FILTER(Customer;[TotalAmountPaid]>Bin[Lower] && [TotalAmountPaid]<=Bin[Upper])))

﻿

However, I'm not really sure what the point of that measure is. ﻿I added the measure as suggested but what do I do with it? At this point, I can just establish a relationship (and a related formula) between the customer table (unique IDs) and the bin table and I will be able to see the bins for each unique customer.

Monday, July 11, 2016 11:29 PM
• Can certainly be done as shown by Michiel,
but here is a version where Power Query does most of the work.
No DAX, no Relationships. All US format.

Excel 2010/2013/2016 Power Query (aka Get & Transform)

Tuesday, July 12, 2016 2:47 AM
• That looks awesome, but how do I create that? LOL.
Tuesday, July 12, 2016 7:07 PM
• I want to do something similar, except I don't care about the number of instances, and there is an additional of criteria of only sum WHEN.
Tuesday, July 12, 2016 7:13 PM
• Since WHEN was sparsely defined, I assumed a monthly interval.
By instances, I assumed customers.
Simply remove Cust from the row field if desired.
Other tweaks can be made once you learn the "M" programming language.
If you are already fluent in DAX, I recommend the "Banding" chapter in the book
"Microsoft Excel 2013, Building Data Models with PowerPivot"
by Alberto Ferrari and Marco Russo.