Forum Discussion
Values aggregate incorrectly with legend
This may be a bug or a feature-change, but maybe there's a way around it in DAX and I'm hoping you can help me.
I have a measure which is as follows:
I have tried a SWITCH with ISINSCOPE, but all I could achieve with that myself is making each [Level2 Group] calculate as -32.15%, and the aggregation was -96.45%, still wrong.
Hi pmay , I would consider using the following formula instead of the above.
% Var (Adjusted EBITDA) = VAR _Actual = [Adjusted EBITDA Actual] VAR _Budget = [Adjusted EBITDA Budget] VAR _AdjustedEBITDA = DIVIDE ( _Actual - _Budget, _Budget , 0 ) RETURN _AdjustedEBITDAThe problem that you are going to run into that Context Transition between Level 1 and Level 2 will not work as you desire. This is why Power BI is adding the -100% to show -300% in the Stack Column Chart.
Note I wondering if this is the best visual to use. Perhaps a waterfall chart would be better. The reason I say this is because you can start with Budget on the Left equal 100% then show how the individual level 2 items contribute to the Actual to Budget column of 32.5% on the left. It appears that you have 3 items at level 2, so 1 of these could be positive and the other 2 negative (e.g. +50%, -75%, -7.5% make up -32.5%)
The adjusted formula could look like the following:% Var (Adjusted EBITDA) = VAR _Scope = ISINSCOPE( Level2) //True or False VAR _ActualFiltered = [Adjusted EBITDA Actual] VAR _BudgetFiltered = [Adjusted EBITDA Budget] VAR _ActualUnfitered = CALCULATE ( [Adjusted EBITDA Actual] , ALL( Level1 ) ) VAR _ActualUnfitered = CALCULATE ( [Adjusted EBITDA Actual] , ALL( Level1 ) ) VAR _Result = IF ( _Scope , DIVIDE ( _ActualFiltered - _BudgetFiltered, _BudgetUnfiltered , 0 ) DIVIDE ( _ActualUnfiltered - _BudgetUnfiltered, _BudgetUnfiltered , 0 ) ) RETURN _Result
4 Replies
Hi pmay , I would consider using the following formula instead of the above.
% Var (Adjusted EBITDA) = VAR _Actual = [Adjusted EBITDA Actual] VAR _Budget = [Adjusted EBITDA Budget] VAR _AdjustedEBITDA = DIVIDE ( _Actual - _Budget, _Budget , 0 ) RETURN _AdjustedEBITDAThe problem that you are going to run into that Context Transition between Level 1 and Level 2 will not work as you desire. This is why Power BI is adding the -100% to show -300% in the Stack Column Chart.
Note I wondering if this is the best visual to use. Perhaps a waterfall chart would be better. The reason I say this is because you can start with Budget on the Left equal 100% then show how the individual level 2 items contribute to the Actual to Budget column of 32.5% on the left. It appears that you have 3 items at level 2, so 1 of these could be positive and the other 2 negative (e.g. +50%, -75%, -7.5% make up -32.5%)
The adjusted formula could look like the following:% Var (Adjusted EBITDA) = VAR _Scope = ISINSCOPE( Level2) //True or False VAR _ActualFiltered = [Adjusted EBITDA Actual] VAR _BudgetFiltered = [Adjusted EBITDA Budget] VAR _ActualUnfitered = CALCULATE ( [Adjusted EBITDA Actual] , ALL( Level1 ) ) VAR _ActualUnfitered = CALCULATE ( [Adjusted EBITDA Actual] , ALL( Level1 ) ) VAR _Result = IF ( _Scope , DIVIDE ( _ActualFiltered - _BudgetFiltered, _BudgetUnfiltered , 0 ) DIVIDE ( _ActualUnfiltered - _BudgetUnfiltered, _BudgetUnfiltered , 0 ) ) RETURN _ResultThanks for your help so far. I will use the better calculation for the variance calculation (actual - budget/budget), I should've spotted that sooner - I was trying to replicate results from Excel to convince my company to move more towards PowerBI - I was lazy and replicated the maths as it was rather than thinking about why.
I thought about your comment, the alternate visuals, and I think I will have to do that in the end. I've watched a lot of hours of YouTube videos and done so many tutorials, but I've never seen a function that I think could modify this behaviour - as you say, it's a limitation in the visual. However, the space for the visual on my page is small, yet the feedback on that particular thing has been ridiculously positive. I have 5 business groups in level 1, DACH, UK, a few more. Each business group has multiple entities within their region. So splitting a single bar for each business group by their individual entities, was a way of really quickly displaying who is contributing positively and negatively to overall EBITDA. A waterfall does show the right data, but it's no better than just removing the legend.
I think I've just tweaked your measure and got the desired result.
My measure looks like this now:
% Var (Adjusted EBITDA) = VAR _Scope = ISINSCOPE('Distinct Entities'[Entity Group]) VAR _ActualFiltered = _Dax[Adjusted EBITDA Actual] VAR _BudgetFiltered = _Dax[Adjusted EBITDA Budget] VAR _ActualUnfiltered = CALCULATE([Adjusted EBITDA Actual], All('Distinct Entities'[Entity Sub-Group])) VAR _BudgetUnfiltered = CALCULATE(_Dax[Adjusted EBITDA Budget], ALL('Distinct Entities'[Entity Sub-Group])) VAR _Result = IF(_Scope, DIVIDE(_ActualFiltered - _BudgetFiltered, _BudgetUnfiltered,0), DIVIDE(_ActualUnfiltered - _BudgetUnfiltered, _BudgetUnfiltered,0)) VAR _AdjustedEBITDA = DIVIDE ( [Adjusted EBITDA Actual]-[Adjusted EBITDA Budget],[Adjusted EBITDA Budget],0 ) RETURN _ResultWhere Entity Group is [Level 1] and Entity Sub-Group is [Level 2] from the earlier example. I swapped the scope to be Group instead of subgroup, and reversed it in the unfiltered variables. You and I were looking at the hierarchies in the opposite direction, is all.
Still just verifying if my other numbers are still correct. I am getting the -32.15% as desired for that region now though.Thanks man. I'm giving up. The headline number was right but the breakdowns were wrong, so I can have one or the other - not both. I'll switch to another visual type, that's the solution.
