Column Total not matching the individual rows dilemma. When a measure is used in a column of a table, it could have different value in the grand Total row which is unexpected and not correct.
A few days ago, I started the trend #MonthOfDAXProblems on LinkedIn and received great feedback. My post garnered comments with many ideas from the community.
In most cases, what user would like to see is the total of rows calculated in the Row Context of that visual, meaning whatever filters are applied, the grand total should only show the Total at the row level. This is not the case with Power BI out of the box and is one of the hot topics these days that we need to persuade Microsoft to change the default behavior to reflect correct value. You can vote this idea here:
Now, its been 8 years so the chances of Microsoft changing this anytime soon are slim. However, to achieve a solution for this and show correct measure total, apply Dax Function SUMX(). This forces the correct row context and gives results based on the rows filtered and are present in the visual and it recreates the summarized table visual based on how it is in the report, and stores it in a table variable. We can then choose to show the individual rows summarized and the same can be used for the Correct grand total.
Let’s have an example where we want to calculate sales of expensive Product Categories.
Expensive is where price > $500, otherwise we consider it cheap and show 0.
To show results at the grand total, we use SUMX() statement and then to show at row level with filters, we will use HASONEVALUE(). Here, DAX Function ISINSCOPE() can also be used.
You will find the link to the Power BI model below.
Please note: For more complex implementations, where RANK() or TOPN() is involved – DAX Function SUMMARIZE() should be used, and we will discuss that in more detail in upcoming posts.
