Edit: The switch is definitely the main culprit. By simply nesting the switch, performance has improved drastically. Almost instant updates to visuals when slicing or changing the switch measure table inputs.
Example, instead of switching between 25 items, I first switch between 5 main categories (costs, volumes, ratios, etc.). Nothing else and the report is now actually totally usable.
Hi all,
My company has a financial report that has a lot of calculations that involve multiple Scenarios/Versions (Actuals, Last Year, Budget, Forecast, vs Bud, vs Fcst, vs LY)
The basic jist is that I have costs and volumes... Let's say a, b, c, d and $a, $b, $c, $d. There are more dimensions like countries and products and those heirarchies I've separated into dimension tables.
My data is currently normalized meaning that it looks like this:
| KPI | Scenario | Value |
| a | Actuals | 1 |
| b | Actuals | 2 |
| c | Actuals | 3 |
| d | Actuals | 4 |
| $a | Actuals | 5 |
| $b | Actuals | 6 |
| $c | Actuals | 7 |
| $d | Actuals | 8 |
| a | Last Year | 9 |
| b | Last Year | 10 |
| c | Last Year | 11 |
| d | Last Year | 12 |
| $a | Last Year | 13 |
| $b | Last Year | 14 |
| $c | Last Year | 15 |
| $d | Last Year | 16 |
| a | Forecast | 17 |
| b | Forecast | 18 |
| c | Forecast | 19 |
| d | Forecast | 20 |
| $a | Forecast | 21 |
| $b | Forecast | 22 |
| $c | Forecast | 23 |
| $d | Forecast | 24 |
My approach was to use tabular editor and create a series of base measures:
a (Actuals) = Calculate(sum(values),Scenario = "Actuals",KPI="a")
Then I need to have the ratios between them, which would be unit costs, as well as versus budget, versus forecast, etc. So, these are all just measures of measures. For example, $a/a (Actuals) = [$a (Actuals)] / [a (Actuals)] or if it were vs Budget then we'd simply have [a (Actuals)] / [a (Budget)].
The main reason for this is that later on we need to calculate how these KPIs are affecting overall cost. Some are fairly standard like a price impact (delta price)*Quantity Actuals. Others are trying to isolate these unit costs above and they can get slightly more complex like
| IFERROR([a/b (vs Budget)]*[$a/a (LY)]*[b (CY)],0) |
Point is, I need to constantly be calculating across scenarios, so I see no way of being able to use my data with a simple matrix. Once all the formulas were built, I wrote a series of measures with switches that reacted to an auxilary table
Something like
a = VAR scenario= SELECTEDVALUE(_PointOfView[Point of View],"CY")
RETURN
SWITCH(TRUE(),
scenario="CY",[a (CY)],
scenario="LY",[a (LY)],
scenario="FCST",[a (FCST)],
scenario="BUD",[a (BUD)],
scenario="vs LY",[a (vs LY)],
scenario="vs FCST",[a (vs FCST)],
scenario="vs BUD",[a (vs BUD)],
blank())
and then the final version called Value, simply did a switch bases on which of these KPIs it was. So, if it's "a" then choose [a]. So, in the end I get each calculation exactly where i need it, and i could add slicers that allow the user to choose whether they want to see Values/Versus and based on certain scenarios. Kind of a DIY pivot table since they love tables. Then the waterfall charts i could limit the options to simply "vs Fcst" "vs Bud" "vs Last Year" since those impacts abovementioned are always comparing actuals to something else.
Well, the report works. The values come as expected. My matrix takes about 5 seconds to refresh, not too bad, the waterfall chart around the same, but on another page i tried to show 3 visuals - By Period (line), and By Country and By Product as bar charts. This takes around 15 seconds.
I tried to change my data to pivoting the scenario into 4 columns - Actuals, Last year, Bud, Forecast, and I saw some improvement.
Am I taking the approach wrong from the beginning? Do you think pivoting the KPIs would work better? There are 20 base KPIs coming from SQL (10 cost, 10 associated volumes), so that would mean 20 columns... Should I break my scenarios into separate facts tables? etc.. I'm not sure what i could possibly do. In the past this was in Excel with a lot of getpivotdata type formulas and while a PITA, it's so far faster than Power BI. The data is around 500k rows, because it's supply chain it has flows like 1 country to anohter, and aroudn 15 different products. This along wiht 12 fiscal periods tends to get the row number pretty high.
Any tips or advice would be appreciated. Thanks in advance.
End result in matrix format is something like this.
| | | Actuals | Last Year | Bud | Fcst | vs Last Year | vs Bud |
| Volumes | | | | | | | |
| | a | # | # | # | # | # | # |
| | b | # | # | # | # | # | # |
| | c | # | # | # | # | # | # |
| Costs | | | | | | | |
| | $a | # | # | # | # | # | # |
| | $b | # | # | # | # | # | # |
| | $c | # | # | # | # | # | # |
| Ratios | | | | | | | |
| | a/b | # | # | # | # | # | # |
| | $a/$b | # | # | # | # | # | # |
| Unit Costs | | | | | | |
| | $a/a | # | # | # | # | # | # |
| | $b/b | # | # | # | # | # | # |
| Impacts | | | | | | | |
| | price | # | # | # | # | # | # |
| | volume | # | # | # | # | # | # |
| | etc | # | # | # | # | # | # |
| | etc | # | # | # | # | # | # |
