How-To Create a Better/(Worse) Variance Report with the ‘New Model’ in SAP Analytics Cloud
Hi all, as an extension to my previous Blog posts:
How to allow a user to perform Version over Version comparative reporting with the Time dimension also in the column axis allowing for drill capability (all for a Total Year or Monthly breakdown comparison).
I wanted to talk about how to take advantage of the ‘New Model’ and enhancing the current limitations with the ‘Account-based’ model when it comes to a very important topic for customers, Better/(Worse) reporting.
What is Better/(Worse) reporting? In short, it’s the ability to take the type of line item into account for reporting purposes. If you’re doing a comparison of this year’s Actuals against the Plan then if your Income-related items increased then this would be a positive variance (Better). However, in the same example if your Expense-related items increased then this would be a negative variance (Worse).
By default, in the ‘Account-based’ model of SAP Analytics Cloud (the only model type that was available until recently), when creating a variance calculation, without going to fairly extreme lengths or workarounds one could not easily achieve reporting that took the ‘Account Type’ property of Account into consideration. By default, the variance calculation will be a simple ‘Base – Comparison’ type of calculation:
As you can see, the above is simply taking Base and subtracting Comparison to calculate the Variance. However, this does not provide valuable insights to someone reading the report as Income and Expense related accounts are treated the same.
In reality, one would want to see a positive or negative variance based off of the type of Account as such:
Now we can see in the same example that our Expenses are being taken into account to flip the sign appropriately so that we know an increase in Expenses is actually negative and vice versa.
So how does one perform the above with the ‘New Model’?
In this How-To I’ll walk through how to create the appropriate ‘Measure’ at both the model-level and Story-level to enable this simple capability.
First, within the ‘New Model’ you will need to create a ‘Calculated Measure’ as such:
Effectively this reads the ‘Value’ Measure which is the measure where all the raw data will get stored under. And depending on the Account Type populated on the Account (either INC or EXP) it will adjust the signage for display and calculating purposes.
Next once you create a Story, you’ll need to create a ‘Restricted Measure’ for ‘Base’ which reads this newly created model Measure, such as:
Then you’ll need to create a ‘Restricted Measure’ for ‘Comparison’ which also reads this newly created model Measure, such as:
Next, you’ll create a simple ‘Calculated Measure’ for this ‘B/(W)’ which will now correctly calculate the variance taking the ‘Account Type’ into consideration:
Finally, as most Users still would like to see both Expenses and Revenues displayed as positives (for the non-Variance columns, within ‘Base’ and ‘Comparison’ itself), you can create duplicated ‘Restricted Measures’ which instead look at the raw data Measure such as:
This concludes our How-To, I hope you have found this blog post useful as I feel it’s important to understand some of the foundational pieces of SAP Analytics Cloud when it comes to planning and reporting and, in this case, how to take advantage of new features of the ‘New Model’.
Please stay tuned for my next blog post which will extend on this Better/(Worse) reporting and making it more dynamic to tie to Version. For example, regardless of the order of the user selection for Base/Comparison selection, for Version to always calculate a variance in a particular order.