It's time to wrap up the Intro to Power Pivot series. This post will work through writing the final three Measures (Var to Budget, Prior Year, and Var to PY).
Budget variance
Let's calculate the variance between Actual and Budget by month.
Start with the workbook created from the last exercise, Measures In-depth, or download this file.
With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.
In the Measure dialog box, select the Table, Actual_Data.
Change the Measure name to Var to Budget.
In the formula box, type =[Actual] - [Budget].
Start typing Actual, and you will get a drop down box to select from.
Measures have a 𝚺 sign next to them and are noted in brackets.
Press Tab to select.
Check formula. Result should be no errors.
Format by changing the Category to Number, with 0 decimal places.
Ok.
= CALCULATE ( [Actual] , Date_LU[Year] = 2012 )
The CALCULATE function can filter a Measure. The CALCULATE function requires first, the Measure, and then the filter. The Measure is [Actual]. The filter is the 2012 year, which is pulling from the Year column of the Lookup Table, Date_LU. Columns and Measures are in brackets.
Notice how the Prior Year column is showing the 2012 Actual numbers for both 2012 and 2013. Using CALCULATE you've told Excel to always show the 2012 actual value regardless of the year presented in the Pivot Table. This is referred to as filter context and occurs because CALCULATE filters take precedent over Pivot Table filters.
💥BEST PRACTICE TIP
CALCULATE is a very powerful function. Always test that the results are as expected. If others in your organization aren't using Power Pivot yet, consider adding a comment to alert the user to the CALCULATE function. 💥
In the PivotTable Fields List, move the Year field from Columns to Filters. Filter to show only 2013.
In the Values area, move Prior Year to the top, so it is the first column in the Pivot.
By the time you wrote your 5th and final Measure for this Intro to Power Pivot series, hopefully, you were starting to get the flow for Measures. Keep practicing, replacing your normal SUM of Values with Measures and trying out a few CALCULATE exercises. Pretty soon Power Pivot will be second nature.
In this lesson, you learned how to:
Start with the workbook created from the last exercise, Measures In-depth, or download this file.
With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.
In the Measure dialog box, select the Table, Actual_Data.
Change the Measure name to Var to Budget.
In the formula box, type =[Actual] - [Budget].
Start typing Actual, and you will get a drop down box to select from.
Measures have a 𝚺 sign next to them and are noted in brackets.
Press Tab to select.
Check formula. Result should be no errors.
Format by changing the Category to Number, with 0 decimal places.
Ok.
Prior Year Measure using CALCULATE
Let's create a Prior Year Measure to show the 2012 Actual values. The formula for creating that Measure is as follows:= CALCULATE ( [Actual] , Date_LU[Year] = 2012 )
The CALCULATE function can filter a Measure. The CALCULATE function requires first, the Measure, and then the filter. The Measure is [Actual]. The filter is the 2012 year, which is pulling from the Year column of the Lookup Table, Date_LU. Columns and Measures are in brackets.
Notice how the Prior Year column is showing the 2012 Actual numbers for both 2012 and 2013. Using CALCULATE you've told Excel to always show the 2012 actual value regardless of the year presented in the Pivot Table. This is referred to as filter context and occurs because CALCULATE filters take precedent over Pivot Table filters.
💥BEST PRACTICE TIP
CALCULATE is a very powerful function. Always test that the results are as expected. If others in your organization aren't using Power Pivot yet, consider adding a comment to alert the user to the CALCULATE function. 💥
Pivot
Let's do some quick clean-up before moving on to our final Measure.In the PivotTable Fields List, move the Year field from Columns to Filters. Filter to show only 2013.
In the Values area, move Prior Year to the top, so it is the first column in the Pivot.
Variance to Prior Year Measure
For our final Measure, let's write the variance to the prior year.
Below is a screenshot of the final result.
By the time you wrote your 5th and final Measure for this Intro to Power Pivot series, hopefully, you were starting to get the flow for Measures. Keep practicing, replacing your normal SUM of Values with Measures and trying out a few CALCULATE exercises. Pretty soon Power Pivot will be second nature.
In this lesson, you learned how to:
- Write Measures which reference other Measures;
- Write Measures to take the difference between columns in a Pivot Table; and,
- Use the CALCULATE formula to create filtered values.
No comments:
Post a Comment