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
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
Solved! Go to 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
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
@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.
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |