Forum Discussion
Helper Vdistinct count with filter & sum
Hi!
I'm trying to get the number of SKUs where the sum in the value column = 0 :
| Location | Fiscal Year+Month+Name | SKU | Value |
| Country A | 2020-P07-Jan | Product A | 13 |
| Country A | 2020-P08-Feb | Product A | 0 |
| Country A | 2020-P09-Mar | Product A | 0 |
| Country B | 2020-P07-Jan | Product A | 0 |
| Country B | 2020-P08-Feb | Product A | 0 |
| Country B | 2020-P09-Mar | Product A | 0 |
| Country B | 2020-P07-Jan | Product B | 0 |
| Country B | 2020-P08-Feb | Product B | 7 |
| Country B | 2020-P09-Mar | Product B | 0 |
| Country A | 2020-P07-Jan | Product C | 0 |
| Country A | 2020-P08-Feb | Product C | 0 |
| Country A | 2020-P09-Mar | Product C | 0 |
| Country C | 2020-P07-Jan | Product B | 0 |
| Country C | 2020-P08-Feb | Product B | 4 |
| Country C | 2020-P09-Mar | Product B | 0 |
I'd like to be able to filter it by the month and the country.
I wrote a formula but it is counting the no of SKUs before summing the values, resulting in some SKU counting as being = 0 and also being >0
I'm looking for a way to count the no. of SKUs after the sum.
Thank you!
Examples based on above table:
| How many distinct SKUs with sum of value = 0? | |
| ALL | 1 |
| Filter on country A | 1 |
| Filter on country B | 1 |
| Filter on Country C | 0 |
| Filter on 2020-P08-Feb | 2 |
| Filter on Country A and 2020-P08-Feb | 2 |
| Filter on Country B and 2020-P08-Feb | 1 |
| Filter on 2020-P08-Feb and Product B | 0 |
Measure automatically sums the count. If I understand you currently you could take a look this:
Measure = CALCULATE(DISTINCTCOUNT('Table'[SKU]),FILTER('Table','Table'[Value]=0))Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
for count of SKU > 0
sumx(summarize(Table[product],"_sum",sum(Table[value])),if([_sum]>0,1,0))for count of SKU > 0, country wise
sumx(summarize(Table[product],table[Location],"_sum",sum(Table[value])),if([_sum]>0,1,0)) Measure automatically sums the count. If I understand you currently you could take a look this:
Measure = CALCULATE(DISTINCTCOUNT('Table'[SKU]),FILTER('Table','Table'[Value]=0))Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
