Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
psmith-nhs-inc
Helper III
Helper III

How to do a count of a count?

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

 

 

1 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] )
)

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

InvoiceTable 
Invoice#Customer# Invoice Amount 
10014 $                637.97
10023 $                456.71
10031 $                619.04
10041 $                992.96
10053 $                987.20
10064 $                194.08
10074 $                  35.97
10081 $                479.84
10091 $                929.18
10102 $                271.85
10113 $                288.53
10121 $                219.49

 

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

 

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

I can get this result:

Customer Name Order_Count 
Joe5
Fred1
Mark3
Joe3

Now I want the Next Level:

Count_Of_Customers_WithOrder_Count
11
23
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.

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] )
)

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.

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.