Free Practice WorkbookIn Power Pivot, one of the major and more powerful feature areĀ Measures. Measures (also known as Calculated Fields in Excel 2013) areĀ formulas/calculations that are added to a Pivot Table. We will work on a simple example to show you how easy it is to add your firstĀ Measure.
Table of Contents
Want to learn about Measures vs Calculated Columns in Power Pivot?
*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***
STEP 1: Select the Sales Table. Go to Insert >Ā Table. Ā ClickĀ OK.
STEP 2: Go to Table Tools >Ā Design > Table NameĀ and give your new Table a descriptive name.Ā In our example, we will name itĀ Sales
STEP 3:
In Excel 2013 & 2016
Select your SalesĀ Table. Go to Power Pivot > Add to Data Model.
This will import your new Table into theĀ Power Pivot Window.
In Excel 2010
Go to PowerPivot >Ā Create Linked Table.
STEP 4:Ā This will open theĀ Power Pivot Window.
TheĀ Sales Table will nowĀ be automatically loaded to the Power Pivot Data Model.
Now Close theĀ Power Pivot Window.Ā
STEP 5:Ā Go toĀ Insert> Pivot Table.
In Excel 2016
SelectĀ Use this workbook’s Data Model. Ā This will use the Data Model you just uploaded in the last step.
SelectĀ Existing Worksheet and chooseĀ your location for your Pivot Table and press OK.
In Excel 2013
Go to Use External Data Source > Choose a Connection
Now selectĀ Tables > This Workbooks Data Model > Open:
In Excel 2010
Go to Use an External Data Source > Choose Connection:
Now selectĀ PowerPivotĀ Data > Open:
STEP 6:Ā Adding a Measure:
In Excel 2016
On theĀ SalesĀ Table, right click and selectĀ Add Measure.
An alternative way inĀ Excel 2016Ā is go to Power Pivot > MeasuresĀ > New Measure.
In Excel 2013
Go toĀ PowerPivot > Calculated Fields > New Calculated Field
(In Excel 2013 “Measures” were renamed “Calculated Fields” and returned to “Measures” in Excel 2016…I know, how annoying!)
In Excel 2010
On theĀ Sales table, right click and selectĀ Add New Measure.
An alternative way inĀ Excel 2010Ā is to select a cell inside the Pivot Table that was created in the previous step and go to Power Pivot > New Measure:
STEP 7:Ā This is where we create our firstĀ Measure.
For Measure Name,Ā type in any name that you like e.g.Ā Total Sales
For theĀ Formula, after the = sign start typing the word SUM.
Just like in native Excel, this will bring up the Formula helper and choose the SUM function by either double clicking on the blue highlighted SUM option or by pressing the Tab keyboard to confirm this suggestion:
After the SUM formula is selected, type in the Table name that we created in Step 2, which we called Sales.
This will bring up the Formula helper and within here you need to select the Sales [Sales Amount] option andĀ close the parenthesis:
Set theĀ CategoryĀ asĀ Currency, and this will automatically handle the formatting for you. Ā Press OK to confirm this.
This Measure will now returnĀ the Sum Total of the Sales Amount column within the SalesĀ Table.
STEP 8:Ā Place your new MeasureĀ Total SalesĀ in theĀ ValuesĀ area.
STEP 9:Ā Now we are able to use our newĀ Measure in theĀ Pivot Table.
Further Learning:
- Importing Excel Workbooks in Power Pivot
- Linking Excel Tables in Power Pivot
- Unpivot Data Using Excel Power Query
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.
30 Days - Full Access 
