distinct count with filter & sum

[dapperscavenger]

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

[V-pazhen-msft · Accepted solution]

@dapperscavenger 

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.

[amitchandak]

@dapperscavenger 

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))