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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RobbeVL
Impactful Individual
Impactful Individual

Return the count of a count

Hi all!

 

I'm trying to count the count of a value.

see screenshot below:

In the first Table you'll see per User the amount orders they placed per month.

 

In the Table below I'd like to count the number of users that have ordered 1 time, >3, >5, >10. As also the % of users that have placed 1 order or >3....

 

Whats the best way to do this?

I already created a table with the outcomes I'd like to see, should I add values to this?,... I'm stuck

2017-08-31_13-36-08.jpg

 

 

1 ACCEPTED SOLUTION

Hi @RobbeVL,

 

[Total Sales] is just a summarize column we defined in the SUMMARIZE function(you can change it to any name you want). For more details about SUMMARIZE function, you can refer to this article. Smiley Happy

Measure = 
IF (
    MAX ( tbl_OrderAmount[Value] ) = 1,
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] = 1
        )
    ),
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] >= MIN ( tbl_OrderAmount[Value] )
        )
    )
)

 

Regards

View solution in original post

5 REPLIES 5
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @RobbeVL,

 

Based on my test, you should be able to use the formula below to create a new measure, then show it with tbl_OrderAmount[Title] column on the report to get the expected result in your scenario. 

Measure = 
IF (
    MAX ( tbl_OrderAmount[Value] ) = 1,
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] = 1
        )
    ),
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] >= MIN ( tbl_OrderAmount[Value] )
        )
    )
)

r1.PNG

 

Here is the modified pbix for your reference. Smiley Happy

 

Regards

@v-ljerr-msft
Awsome ! seems to work!

Only wondering what is this refering to?

 [Total Orders]

 

regards,

 

Robbe

Hi @RobbeVL,

 

[Total Sales] is just a summarize column we defined in the SUMMARIZE function(you can change it to any name you want). For more details about SUMMARIZE function, you can refer to this article. Smiley Happy

Measure = 
IF (
    MAX ( tbl_OrderAmount[Value] ) = 1,
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] = 1
        )
    ),
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                General_Deliveries,
                General_Deliveries[Name + Last Name],
                "Total Orders", COUNTROWS ( General_Deliveries )
            ),
            [Total Orders] >= MIN ( tbl_OrderAmount[Value] )
        )
    )
)

 

Regards

Greg_Deckler
Super User
Super User

You are probably going to need to create a meause in which you use a SUMMARIZE to group your items and the measure will analyze the rows in that SUMMARIZE'd table and provide you with the counts. For an example of this, see this article:

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 

Different scenario and calculations but you should be able to use the same concept.



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...

@Greg_Deckler

 

I'm completely stuck with  this one, anyone that could help me out with this example file?

 

https://www.dropbox.com/s/47qbl4f5vquzw3o/DatamodelParcify_CSV_RobbeUpload.pbix?dl=0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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