Distinctcount if sales 0

[leejayd]

Hi,

I have a fact table with CustomerNo, DateKey, ProductKey, SupplierKey, Sales.

This is related to a Calendar table, Product, and Supplier Table.

I'm trying to calculate a distinct count of customer where the sales = 0 in a specific year and keeping the original filter conditions on related tables.

I've tried :

Calculate(Distinctcount(fact[CustomerNo), Fact[Sales]=0)

This won't work as the critiera needs to assess the Sales over the whole year and only return 0 if all months are 0.

Any idea or pointers would be a big help.

 

[scottsen]

I think something like this:

[Total Sales] := SUM(Fact[Sales])
[Count of NoSale Customers] =CALCULATE(COUNTROWS(VALUES(Fact[CustomerNo])), FILTER(VALUES(Fact[CustomerNo]), [Total Sales] = 0))

If you had a Customer table, you would work against that... but with that absense, the VALUES(Fact[CustomerNo]) kinda creates a virtual version of a unique customer table.

 

[leejayd]

Thanks Scott. I ended up using summarize to group by year and then filter. I wonder which is the more optimal.

Will run a test when I get a minute.