Filters in CALCULATE override previously existing filters on the column they use. For example, CALCULATE ( [Sales], Product[Color] = “Red” ) returns red sales, no matter what you filter in a pivot table. Nevertheless, this golden rule sometimes looks not to work as expected, and it is time for another puzzle!
Hurry up, this puzzle has a limited lifetime, since it depends from a behavior that the team is already fixing, it might be outdated in a few weeks!
Scenario
The data model contains only two tables: Sales and Date. A relationship links the two tables based on the date key.
The Challenge
You build two measures using Power BI Desktop: one computes the sum of sales, the other one computes only the sales of a single month, March.
Sales[SumOfSales] :=
SUMX (
Sales,
Sales[Quantity] * Sales[Net Price]
)
Sales[SalesOfMarch] :=
CALCULATE (
[SumOfSales],
'Date'[Month] = "March"
)
Once the measures are ready, you build a dashboard that shows sales of selected month and year in the first column, and the sales of March in the second column. Unfortunately, something weird happens: sales of the individual months are reported correctly, whereas the column with the sales of March, instead of repeating the same value for all the rows, shows a value only for March.
Because of CALCULATE’s filters argument syntax, the code of SalesOfMarch is equivalent to this:
Sales[SalesOfMarch] :=
CALCULATE (
[SumOfSales],
FILTER ( ALL ( 'Date'[Month] ), 'Date'[Month] = "March" )
)
The ALL function should remove any filter from the month before applying the filter over March. That statement is totally true, but something different is happening in the report that prevents the expected behavior.
Your task is to find where the problem is and to find the correct formulation for the measure, so that the value returned by SalesOfMarch measure is always the same number for all the months, corresponding to the March value of SumOfSales measure.
Hints
- Did you notice that the first month is January and not April?
- This behavior cannot be reproduced in Excel, which works as expected.
Solution
To see the solution, just press the button below.