How to do a count of a count?

[psmith-nhs-inc]

OK, pretty simple question, I think.  I have customers.  They have invoices. Using Slicers, I can choose a customer, and show many invoices they have, or build a matrix to show each customer and how many thay have each.

I can choose a date range, and show how many total invoices were shipped within that range, with a matrix by customer.

What I want to show, however is how many customers ordered exactly 5 times, how many ordered exactly 4 times, how many ordered exactly three times, etc. within that date range.

I am not sure quite how to go about this in DAX.  I can get specific if I need to, but I think just the general concept would be enough to get me where I need to be.

Thank for any assistance.

Phil

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[psmith-nhs-inc]

InvoiceTable
Invoice#	Customer#	Invoice Amount
1001	4	$                637.97
1002	3	$                456.71
1003	1	$                619.04
1004	1	$                992.96
1005	3	$                987.20
1006	4	$                194.08
1007	4	$                  35.97
1008	1	$                479.84
1009	1	$                929.18
1010	2	$                271.85
1011	3	$                288.53
1012	1	$                219.49

CustomerTable
Customer #	Customer Name
1	Joe
2	Fred
3	Mark
4	Nathan

With this DAX: Orders_Count = Calculate(DISTINCTCOUNT(InvoiceTable[invoice#]))

I can get this result:

Customer Name	Order_Count
Joe	5
Fred	1
Mark	3
Joe	3

Now I want the Next Level:

Count_Of_Customers_With	Order_Count
1	1
2	3
1	5

If I know how to count the count, I should be able to figure out how to Slice and sort by count.  For instance, Customer with most orders on top.  Customers with 100 orders.

I am trying to wrap my head around the suggestion offered in the previous post.  It is not quite gelling for me.

Thanx for any help.

[Zubair_Muhammad · Accepted solution]

Hi @psmith-nhs-inc

Try this Calculated Table

from the Modelling tab >>New Table

Table =
SUMMARIZE (
    SUMMARIZE (
        CustomerTable,
        CustomerTable[Customer Name],
        "Order_Count", COUNTROWS ( InvoiceTable )
    ),
    [Order_Count],
    "Count of Customers with", COUNT ( CustomerTable[Customer Name] )
)

[psmith-nhs-inc]

Thank you.  I was able to adapt that to my PBIX, and now I understand how Summarize works.  Previous explanations were a little too generic to wrap my head around.

Thank You.

[Zubair_Muhammad]

@psmith-nhs-inc

See file attached here.

It works with the sample data

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149