Forum Discussion
Values aggregate incorrectly with legend
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
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.
