Return the count of a count

[RobbeVL]

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

[v-ljerr-msft]

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

Here is the modified pbix for your reference. 

Regards

[RobbeVL]

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

Only wondering what is this refering to?

 [Total Orders]

regards,

Robbe

[v-ljerr-msft · 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. 

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]

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.

[RobbeVL]

@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