Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am totally lost on how to count when the count is dependent on a sum, and not just a value.
1. How do I count the number of 0 sales for Product Category per Store in table below. The way I am thinking, it should be 2 in this example (Star|Alfa and Star|Bravo).
2. How do I count the number of Stores with 0 sales on one or more Product Category? The way I am thinking, it should be 1 (Star)
Store | Product Category | Product | Sales |
Star | Alfa | A1 | 0 |
Star | Alfa | A2 | 0 |
Star | Bravo | B1 | 0 |
Star | Bravo | B2 | 0 |
Moon | Alfa | A1 | 0 |
Moon | Alfa | A1 | 8 |
Moon | Bravo | B2 | 10 |
Moon | Bravo | B2 | 0 |
I would deeply appreciate any input.
/depple
Solved! Go to Solution.
Hi @depple
I advice you to create, two dimensions, one for product and one for stores:
and then you can have:
Measure = COUNTROWS( FILTER( SUMMARIZE( Data, Products[Product Category], Stores[Store] ), CALCULATE( SUM( Data[Sales] ) ) > 0 ) )
Measure2 = COUNTROWS( FILTER( SUMMARIZE( Data, Stores[Store] ), CALCULATE( SUM( Data[Sales] ) ) > 0 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi,
This measure works
Hi @depple
I advice you to create, two dimensions, one for product and one for stores:
and then you can have:
Measure = COUNTROWS( FILTER( SUMMARIZE( Data, Products[Product Category], Stores[Store] ), CALCULATE( SUM( Data[Sales] ) ) > 0 ) )
Measure2 = COUNTROWS( FILTER( SUMMARIZE( Data, Stores[Store] ), CALCULATE( SUM( Data[Sales] ) ) > 0 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo,
Thank you so much for your help! I understand that I have a long way to go in learning how Power BI works.
With a tiny tweak, I got measure 1 working just fine!
On measure 2 I am struggling a little. As far as I can observe, it counts the number of Store with 0 sales in all Product Category (zero sales totally), but I need it to count number of Store with 1 or more zero Product Categories with zero sales. In other words, Store that do not sell in any Product Category. Do you think it is possible?
Hi,
This measure works
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |