Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

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

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.

New Member

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

Super User
Super User

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):



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)



@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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?

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.

@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.'

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors