Difference in Calculated Column value when displayed in Card vs in Table

[tsf]

I have a customer sales table in my model, with CustomerNumber and YearMonth columns. All other fields are sums (sales, gross margin, etc.). I want to get a count of customers who have sales < $100 across a 12-month timeframe (summed, not broken into individual months). I added two calculated measures:

1. [Sales $] = SUM(TotalSales)
2. [# Customers with Small Sales] = CALCULATE(DISTINCTCOUNT('Customer Data'[CustomerNumber]), FILTER('Customer Data', [Sales $] < 100))

Here's where things don't add up:

Notice on the left table, I correctly see 5 customers who have sales < $100 for the 12-month period. But also notice that when I place the calculated column "# Customer with Small Sales" into a Card (shown on the right), it shows 154 customers as meeting that criteria, which is incorrect. I don't understand why this is the case. What am I missing? What might cause the difference here? Thanks.

[Jeetendra]

Your Filter Part is not doings its job make sure that you have write the logic correctly.

[Greg_Deckler]

I tried replicating your formulas in a simple table but couldn't get them to work at all. I'm not sure what "TotalSales" refers to, it is input like a table??

In any event, here is what I did, hopefully it will point you in the right direction for your model.

First, a "CustomerSales" table (purely a guess on my part):

CustomerNumber   YearMonth    Sales    
251835	20151	0
252316	20151	0
252317	20151	0
274626	20151	0
298259	20151	0
251835	20152	0
252316	20152	0
252317	20152	0
274626	20152	0
298259	20152	54
251835	20153	0
252316	20153	0
252317	20153	0
274626	20153	0
298259	20153	0
100000	20151	100
100000	20152	100
100000	20153	200

Next, a "Customers" table (you could pull this from the same data, just grab the distinct values):

CustomerNumber
251835
252316
252317
274626
298259
100000

Relate the two tables on CustomerNumber. In Customers table, create the following two custom columns:

TotalSales = SUMX(RELATEDTABLE(CustomerSales),CustomerSales[Sales])

Is Small Customer = IF([TotalSales]<100,1,0)

Bob's your uncle. (maybe, I'm not really up on your family tree)

[tsf]

I forgot - regarding your suggestion of creating a new colum as follows:

TotalSales = SUMX(RELATEDTABLE(CustomerSales),CustomerSales[Sal​es])

I don't think I can use that. That assumes I'm always only going to want to base my [# Customer with No Sales] measure off of the entire 12-month data set. But that isn't the case - I might want to look at it for only 6 or even 3 months. But if those are calculated columns (not measures) then that would sort of hard-code a TotalSales value based on however many months I have in my 'Customer Data' table, and that in turn wouldn't respond correctly to any Filters or Slicers for the months.

[tsf]

Thanks for your help. I've purposefully stripped my multiple tables down to one (denormalized) so that I took out any potential issue with my joins. Here's what I'm seeing when I add my calculated measure to a table with CustomerNumber and the Sales $ measure. Notice that the first 4 listed actually show $0 total sales for my data set. However, notice the other two in there (highlighted in yellow) which also show a 1 in the [# Customers with No Sales] calculated measure, even though they both have positive sales for the year.

The reason? It is because when you shows these sales grouped by month, both of these customers have at least one month where Sales = $0. For example, here's one of them below. (BTW, this month with $0 is included in the data because there are other columns that I'm not showing where other costs were incurred even though no sales exist, thus it shows up in the data set.)

But I simply don't understand why this matters, because my calculated measure is SUMMING the sales. And in turn, I'm asking for a count of customers where the SUMMED sales equals $0. So the above customer with $54 for the year should not get flagged as having $0 sales (i.e. $54 + 0 = 54). Again, for reference, here's the measure definition:

# Customers with No Sales = CALCULATE(DISTINCTCOUNT('Customer Data'[CustomerNumber]), FILTER('Customer Data', [Sales $] = 0))

For some reason this calculation isn't working as I intend, but I can't figure out what is wrong with it.

BTW, Bob is my uncle. How did you know him?

[tsf]

Just read something that I hadn't picked up on before. Perhaps this is the reason why I see the strange results. The filter parameter for the FILTER DAX function has the following explanation:

"A Boolean expression that is to be evaluated for each row of the table."

If it is evaluated for each row, then it appears it might be doing this prior to summing the Sales field for all months, in which case it would include in the count the months that showed $0 in sales. This isn't what I want, but I guess that makes sense. My question is whether there is a function that sums first, then does a distinct count.

[vanessafvg]

@tsf did you ever get the answer to that question?

'My question is whether there is a function that sums first, then does a distinct count.'

[tsf]

I don't think so. But then again, it was a while back when I asked the question so I don't remember.