locked
Help with creating bins in powerpivot RRS feed

  • 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

Answers

  • 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.
    I updated the link.

    • Proposed as answer by Charlie Liao Sunday, July 24, 2016 2:53 AM
    • Marked as answer by Charlie Liao 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
    Answerer
  • 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.
    http://www.mediafire.com/download/ktudibx5gj817tu/07_11_16.xlsx
    http://www.mediafire.com/download/kb8gandcbtxw2e5/07_11_16.pdf

    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.
    I updated the link.

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