Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |