Mynda,
I really enjoy your courses and have learned a lot!
I am new to DAX and have been able to write some simple formulas but I am having a hard time with this one. I am trying to write a new measure that will count the number of customers with a total of zero sales for a product. My unique customer table is called MY TCL. Sales data is in table, Sales Data. Tables are linked by [Name] and [Accounts]. I can get the result with the cool Q/A feature in PBI, but I would like to have the measure to use elsewhere in my Model. Thanks in advance!.
Hi David,
You can use this measure:
=CALCULATE(DISTINCTCOUNT(Table1[Accounts]),FILTER('Table1','Table1'[Sales]=0))
Mynda
Hi Mynda,
that is was what I tried along with other measures. The problem is it counts all the accounts because each account will have a time period that they didn't buy anything. I want to count those accounts that have no sales , "0", in each time period. SUM=0. So, accounts that have yet to purchase anything over all time periods. Does that make sense?
Hi David,
You didn't mention the date dimension, so I didn't allow for that. Please provide a mockup of your desired result based on your sample data that shows how you expect the dates to factor into the results.
You may need to change your sample data because you only have two customers, one with all zero sales and one will no zero sales.
Mynda
Here is a larger sample file. Thanks.
Hi David,
Please see the file attached. I used a simplified version of your sample data just to make it easier to follow. I hope that's what you were after. The issue of grand totals where rows have a measure that is designed to calculate at a row level causing the grand total to return zero is explained here.
Mynda
Mynda,
that's perfect! Thanks for the help.
David
