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.