Count distinct products where sum of sales are equal to zero

009co — Fri, 10 Jun 2022 21:33:38 GMT

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 )

Re: Count distinct products where sum of sales are equal to zero

tackytechtom — Fri, 10 Jun 2022 21:50:02 GMT

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/

topic Count distinct products where sum of sales are equal to zero in DAX Commands and Tips

Count distinct products where sum of sales are equal to zero

Re: Count distinct products where sum of sales are equal to zero