Calculating Variance using Cross calculations in SAC
In this blog post, we are about to see how to calculated the Current year (CY) Vs Previous Year (PY) variance using cross calculation in SAC.
Let’s say we have created the model with the below dataset.
Steps to find the Variance:
- Create a model with the above sample data.
- Create a story with the above created model
- Drag and drop a table with the below measures and dimensions
- To find the variance, create cross calculation by enabling the check box under Columns as(on the right pane) shown in the below screen shot.
- After adding the cross calculations, add calculations as shown in the below screenshot
- When you add calculation, a new window pops up. In that, select ‘Restricted Measure’ in order to find CY Revenue by restricting the Measure (Revenue) to the current year as shown below.
- To do the step 9, Select the measure as Account Values (as the Revenue has to be restricted), Select the Dimension as Year (as we are going the restrict the Revenue based on the year dimension) and select the values as per your requirement(In our case, the value should be 2020 as it is the current year).
- Similarly, create another Restricted Measure for previous year.
- Inorder to find the variance, Create a ‘Calculated Measure’ with the formula CY Revenue – PY Revenue
- Now you will be able to see the CYPY variance in the table as shown in the below screenshot.
So, from the above steps, we can calculate the variance using cross calculation.