SAP Analytics Cloud - Variance Reporting using Tables & Cross Calculation

[DebjitSingha]

ℹ️

Variance reporting is a very common requirement. Sometimes variance calculations are required across dimensions and sometime across category/ versions. Charts offers variance option out of the box. This option works for comparing dimensions as well as comparing numbers across category.

Charts and numeric pointer are good way to show variance, although most of the time business users requires these numbers to be presented in tabular format. No wonder they love pivot tables in Excel 🙂

⚠️ Known limitations:

Table widget do not offer out of the box variance functionality. We need to create calculated measure to create variance column. Now creating calculated measure may not be enough in scenarios where you want to compare numbers across category/ versions (e.g. actuals vs. forecast). Calculated measure only works with one category/ version at a time. This means if you use calculated measures only you may end up with these - Q&A : Calculation works only with one category

🏷️More often than not, we see similar requirement and post on SAP Community. What seems to be very common and simple requirement may not have a straight setup process. Here are couple of examples:

Q&A : Variance Report between dimensions and version
Q&A : SAP Analytics Cloud: Variance between versions in table
Q&A : Calculation in table across versions
Q&A : Difference between Category Members

For someone who is starting SAC journey this may turn out to be little tricky. In this blog post, we are going to cover steps involved in Cross Calculation to create variance report in table formation. In-fact cross calculations are built for such requirements.

Adding Forecast and Actuals by Product Type. Adding forecast and actuals may not be exactly what you are looking for :). However, this will give you an idea how to setup your scenario.

🏷️This is how the final output would look like.

You can switch rows and columns as pre your requirement.

📌 Steps

1. Add cross Calculation under rows.


2. Locate under rows > Cross Calculation > More> Add Calculation > Restricted Measure for Actuals


3. Create restricted measure for Forecast as well:
   


4. Add another calculation under cross calculation. This time it should be Calculated Measure type. Like below-
   


5. Now remove Account dimension from rows and leave Cross Calculation.


6. You can change the order of individual Cross Dimensions –


7. Now that restricted measure are appearing under calculation, you can calculate % contribution and other similar variance as well.

👍Thanks for your time. You made it this far🦉 you are on 🔥 keep learning.. Feel free to leave your comments…..

[mirzahaque]

Thanks!

Indeed very common requirement and a very simple solution if you know the steps.

[achab]

Crisp & clear blog - thanks for creating & enabling the community!

This workflow should be easier to achieve IMHO, even if implementation is feasible once you know it.

Best,

Antoine

[DebjitSingha]

Thanks Antoine

[SebastianBo]

Hey debjit.singha,

 

thanks for this blog post regarding variance calculations. Currently, I am facing another problem when calculating a variance. Perhaps there is also a suitable answer to this.

How can I calculate the variance between 2 versions dynamically if the version is not known to me before ?

Explanation: I have an official public forecast version for a planning application. In addition, users can create their own private version (for "what-if simulations "). Now the user wants to compare his own plan values with the original forecast via variance.

How can I compare his own private version with the public forecast version ? Since he created the private version later, there is no restricted measure.

[rkmody]

When I tried to add a cross calculation under rows, the option was not there. It allows for calculated dimension, but not cross calculation, maybe there was an update. Any other work around?

[DebjitSingha]

If you are using New Model type then the behavior is a bit different. Cross calculation appears only if you are not using account dimension in new model. Check below Q&A

Q&A : 'Cross calculations' option is not available in Stories built on top of 'NEW Model'

Or may be you have applied browser based blending (at story level). Cross Calculations are not yet supported in bended setup.

https://userapps.support.sap.com/sap/support/knowledge/en/2743980

Or cross calculation is not yet supported on live connection scenarios (except live HANA). In case of BW live connection, one can convert ADSO setuop to HANA View and then use that view in SAC live setup. This will let you use smart feature and supports Cross Calculation as well.

 

 

Thanks,
Debjit

[DebjitSingha]

You can have input control created on version dim should let you do this.

Sorry, saw you message today. Hope you found your answers already.

Thanks,

Debjit

[rkmody]

I have to use the account dimension in my model. What other way is there to do this then? I just want to be able to create a new column in the model, where I take 1 column of data and subtract it from another column of data.

[DebjitSingha]

You can created calculated measure at story level or at the model calculation itself. Beside if you are using Table widget, you can do it using table functions. Add new row or column in table widget and then use simple formula (like Excel).

Thanks,

Debjit

[aizhan_k]

Hi Debit,

thank you very much for the post.

Is there any way to calculate and visualize variance on the chart (not table) over the members of one dimension? So far I know it is possible for time dimension where one measure can be comapred to the previous period's value.

How can I e.g. show variances when comparing costs between production stages?

Any tip would help.

Thank you!

Best regards,

Aizhan