Forum Discussion
Helper IDifference in Calculated Column value when displayed in Card vs in Table
Community ChampionI 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)
Helper II forgot - regarding your suggestion of creating a new colum as follows:
TotalSales = SUMX(RELATEDTABLE(CustomerSales),CustomerSales[Sales])
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.