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…..