How does granuality effect Totals

DAX DAX Calculations
1

Hi All,

I have been working my through the Mastering DAX Calculations course and working with many of the functions but I have come up with a problem and don’t undestand the cause and as a result can’t work out the solution. Can you please help. Probably not surprisingly, it is about totals.

I have created a Sales measure, = SUM(Sales[Total Revenue]), and an All Sales Measure, = CALCULATE([Sales], ALL(Dates[Date]) ). The objective may be to calculate the ratio of the daily, monthly or yearly sales to the overall total, for example. If I put the measures in a table with a column of specific dates, it works perfectly but if I use a column of months, I end up with the month totals with the All Sales measure rather than the overall total.

As I understand it, the All() function removes all the filters (being the dates) and therefore delivers a fixed total. But what is not clear is why it works at the lowest level of granuality (being specific dates) but not the next level up, being Months ( and presumably quartes and years).

PracticeModel.pbix (404.2 KB)

Can you please explain and provide a solution.

Thank you
MG

2

Instead of ALL(Dates[Date]) use ALL(Dates), because you’ve passed the Date column it currently only ‘works’ on the highest cardinality.

3 Likes
3

It always seems to be something simple. Learning curve, I guess.

Thanks Melissa

MG

4

Hi @MIchaelGordon, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!