Count distinct products where sum of sales are equal to zero

[009co]

I want to count the number of distinct products where sum of sales are equal to zero.

Product and sales query are related by product.

The sales query may not have all products so the product must come from the product query not from the sales query.

Couple of methods below have been tried and not working:

SUMX(VALUES('Product'[Product Name]), IF(SUM(Sales[Amount]) = 0, 1))

CALCULATE(COUNT('Product'[Product Name]), FILTER(ALL('Sales'), SUM(Sales[Amount]) = 0 ))

CALCULATE(DISTINCTCOUNT( 'Product'[Product Name] ), FILTER (ALL( 'Product'[Product Name] ), SUM('Sales'[Amount]) = 0 )

[tackytechtom · Accepted solution]

Hi @009co ,

How about this one here:

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Product'[Product Name] ),
    FILTER (
        VALUES ( 'Product'[Product Name] ),
        CALCULATE ( SUM ( 'Sales'[Amount] ) ) = 0
    )
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/