If you haven't come up with the wrong totals in Power BI yet, you're either lucky or you have come up with them but nobody has noticed it including you, and in that case, you're still lucky.
I have created a What If scenario to see how my sales would change, if my sales rise up to some specific percentage, for example, %100. If you don't know what is that or how to make scenario analysis you can watch DAX Fridays! #53: GENERATESERIES. Anyway, the first measure "Sales" shows the sum of sales. The second one shows the sales if, the sales rise up %100. And the measure of that is:
Sales Scenario Value measure brings the selected percentage:
So far, everything works just fine. Here comes the measure for the third column:
In the third column, I want to apply a %100 sales increment scenario only for one product, called Canon Ink, Color, and see how that affects my total sales. For Canon Ink, Color, I apply the %100 increments on the sales scenario, and for the other columns, I want to see their real sales. As a result, in the third column, only the price for Canon Ink, Color doubled, others stay the same. But the problem is total. Despite the measure doubled the price for the selected product, it doesn't reflect that to the total line. Total line stays like there is no price change at the selected product. Why is this happening? Because Power BI is not calculating the Total Line by iterating row by row the upper columns. That's why we need to find a way to force Power BI to do the calculation row by row. That means we need to calculate it by using an iterator function, not an aggregator.
To be able to do that, I have used the SUMX function. Iterator functions need a table to iterate and I have virtually created the table to iterate row by row. To create a virtual table inside the measure, I have used the SUMMARIZE function which asks which column to group, and a measure to do the grouping. After creating the virtual table, I have used Scenario For Canon Ink, Color measure as the expression.
For this situation, this method has worked. For your situation, it may not, and you might need a different solution. The thing to remember with wrong totals is somehow we need to find a way to force Power BI to do the calculation row by row, not by aggregating.
Comments