Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
45 |