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

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Select the Sales Table. Go to Insert > Table.  Click OK.

Measures 01

 

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

Measures 02

 

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.

Measures 03

 

In Excel 2010

Go to PowerPivot > Create Linked Table.

Excel 2010 - 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.

Measures 04

 

Now Close the Power Pivot Window. 

CLOSE POWERPIVOT WINDOW

 

STEP 5: Go to Insert> Pivot Table.

Measures 05a

 

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.

Measures-05

 

In Excel 2013

Go to Use External Data Source > Choose a Connection

excel 2010 measure connection

 

Now select Tables > This Workbooks Data Model > Open:

2013 - Existing Connections

 

In Excel 2010

Go to Use an External Data Source > Choose Connection:

excel 2010 measure connection

 

Now select PowerPivot Data > Open:

2010 - Existing Connections

 

STEP 6: Adding a Measure:

In Excel 2016

On the Sales Table, right click and select Add Measure.

Measures 06

 

An alternative way in Excel 2016 is go to Power Pivot > Measures > New Measure.

2016 - Add 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!)

2013 - Add Measure

 

In Excel 2010

On the Sales table, right click and select Add New Measure.

2010 - Add 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:

new measure excel 2010

 

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:

sum

 

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:

sales amount

 

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.

Measures 07

 

 

STEP 8: Place your new Measure Total Sales in the Values area.

Measures 08

 

STEP 9: Now we are able to use our new Measure in the Pivot Table.

Measures 09

 

Measures

 

 

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Enabling Power Pivot in Excel 2013 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel. Read the tutorial on how to install Power Pivot in Excel 2010 here Read the tutorial on how to enable Power Pivot...
Power Pivot is now available in all versions of Ex... Microsoft have just announced that Excel Power Pivot will now be available in all Windows editions of Excel, which is awesome news and a step in the right direction. Here is what they announced: I’m happy to announce that your input has had another direct impact on the produc...
Installing Power Pivot in Excel 2010 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel. Read the tutorial on how to enable Power Pivot in Excel 2013 here Read the tutorial on how to enable Power Pivot ...
COUNTROWS in Excel Power Pivot   In 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 use the COUN...