Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |