In this blog let's take a look at why your DAX calculations are returning 100% instead of a fraction of the grand total.
In DAX you can compute % Over Total using this pattern
But sometimes the same pattern will return 100% everywhere, why?
To understand this behaviour we need to first understand how Cross Filtering works in DAX.
In the same report if we test for ISFILTERED ( Dates[Month] ) then it will return TRUE at individual cells and FALSE at grand total
At Grand Total the engine only does a separate scan of the DAX code that you write so there is no filtering happening nor is the grand total a sum of all the rows of the visual.
In a simple scenario of SUM the DAX engine can sum the rows because there is no external filter context and the calculations are additive but it is always better to say that it is a separate scan because in case of non additive measures like a complex DISTINCT or a Running Total it is not a SUM of rows of the visual
But if you have a slicer with Months then you are applying a filter over the visual and in this case the grand total will also be filtered.
So what about ISCROSSFILTERED DAX Function? Because a Column Filters itself but it also crossfilters itself and in this case ISCROSFFILTERED will return a true.
Now we are so close to figuring out the issue, since Month is a text field that has a specific sort order Jan -> Feb -> March we all use the Sort by column property to sort Month Name by Month Number and this Month number column is what is causing all the issues.
So let's test the ISCROSSFILTERED with Month Number:
As you can see it returns true for Crossfiltered but also for Filtered the reason being there is a slicer that is also filtering the visual so "Is Month Filtered" has to return true because there are direct filters on that column.
As you can see after clearing the slicer now it returns False for Is Filtered but True for Is Crossfiltered.
So why is this information useful? Simply because when a column (Month Name) is being Sorted by another column (Month Number) and the Column (Month Name) being sorted is in a Visual then that Sort By column (Month Number) is used internally by the Visual and you can see that by capturing the events in Performance Analyzer.
So going back to the scenario where Month Name is used in the visual
Now I have captured the DAX Queries being used for preparing this visual, which looks like this:
I can clean it up as well to make it more readable:
So even though the visual in PBI didn't have the Month Number column but due to the Sort By property being used on the Month Name column, the engine has to include that column in the prepration of the Visual.
Now since Month Number is being used behind the scene it poses a challenge to our [% Over Total] Measure, as we have seen Month Number is cross filtering Month Name, when we remove the Month Name from Filter Context with REMOVEFILTERS we are ignoring the Crossfilter active from the hidden Month Number column and to fix this we must include Month Number as well inside the REMOVEFILTERS to remove the Crossfilter effect.
Once done your code will start working just fine.
Now this was just one example, there are many such situations where the code will return 100%.
For example the following example of TOP 3 Months should show Total Sales for only top 3 months but it only shows the Total Sales of that month for each month
Why? Simply because we have used ALL ( Dates[Month] ) and didn't include the Month Number column to ignore the crossfilter effect.
Once you include that it works:
Same goes for RANKX:
It won't work unless you add that hidden column:
So going forward be sure to include that sorting column in your DAX code if you're trying to remove the sorted column from the filter context.
Hopefully I was able to provide a reasonable explanation on why you get a 100% in your visuals.
Comments