Here, we will learn how to create a sales variance analysis year over year. A year over year variance calculates the difference between the current year and previous years to determine whether sales are increasing or decreasing. We will use a Table chart to understand the calculation in a simpler manner, but this function can be used in others charts, such as Bar, Line, and Combo.

We will add the visualization, Table chart with the dimension OrderYear and master measure Sales $.

Go through the following steps to create the visualizations:

1. Click on the Edit button to enter the edit mode.
2. Click on the Charts button on the asset panel, which is on the left-hand side of the screen, and find the Table chart.
3. Click on the Table chart, and then drag and drop it into the empty space at the top of the sheet:

4. Click on the Add dimension button and select Year in the Dimensions section.
5. Click on the Data heading in the properties panel to expand it.
6. Find the Add column button, click on it and choose Measure.
7. Select Sales $ in the Measure section.
8. Fix the measure Number formatting to show a number with two decimal places.
9. Click on the Add column button again to add the second measure.
10. Click on the fx button to open the expression editor, and then type the expression for the new measure, Above(sum(SalesAmount)).
11. Click on the Apply button to close the expression editor and save the expression.
12. In the Label of the measure, type Sales LY.
13. Fix the measure Number formatting to show a number with two decimal places.
14. Click on the Done button to enter the visualization mode. The Table chart will look like this:

15. Click on the Edit button to enter the edit mode.
16. Go to the properties panel and click on the Data heading to expand it.
17. Click on the Add column button again to add the third measure.
18. Click on the fx button to open the expression editor, and then type the following expression for the new measure: Column(1)/Above(sum(SalesAmount)).
19. Click on the Apply button to close the expression editor and save the expression.
20. In the Label of the measure, type Sales Variance.

21. Fix the measure Number formatting to show the number as a percentage with two decimal places. The Table chart will look like this:

We learned how to calculate a year over year variance. We used the Above() function in the Sales LY column to retrieve the value of the previous record; that is, the previous year. The Sales Variance calculates the percentage between the value from the current record and the previous record. On this column, the value for the current record was retrieved using the Column(1) function with the number 1 as a parameter to get the value from the first measure.

You can only use the Column() function to reference measure columns.