Using Measures Power Pivot

Using Measures Power Pivot

Measures is a very powerful and vital feature in Power Pivot.

Measures are fields that have been calculated in the 2013 version of Microsoft Excel and have been included in a Pivot Table.

Here is an easy examples on how your first measure can be added to your Pivot bable

Highlight Sales Table, click on the Insert tab, then table and select OK

Measures 01

After that, you click on Table Tools, select design and then table name. Give a name that is descriptive to the table. In this instance, sales will be the name we will assign to the table.

Measures 02

For the 2013 and 2016 versions of Microsoft Excel

Highlight the sales table, click on the Power Pivot tab and select Add to Data Model

Your table, which in our case was named sales, will be imported into the Window for Power Pivot

Measures 03

For the 2010 version of Microsoft Excel

Click on the Power Pivot Tab and select Create Linked Table

This will result in the window for Power Pivot being opened.

The table named sales will then be loaded to the Data Model for Power Pivot automatically.

Measures 04

You can now close Window for Power Pivot.

CLOSE POWERPIVOT WINDOW

Go to the Insert tab and select Pivot Table

Measures 05a

For the 2016 version of Microsoft Excel

Pick the option labeled as Use this workbook’s data model. The data model that was uploaded previously will be used.

Choose the option labeled existing worksheet and select where you want the Pivot Table to be placed, and then select OK.

Measures-05

For the 2013 version of Microsoft Excel

Click on the option labeled Use External Data Source and then select Choose Connection

excel 2010 measure connection

Next, you click on Tables, choose This Workbooks Data Model and then select Open

2013 - Existing Connections

For the 2010 version of Microsoft Excel

Click on the option labeled Use External Data Source and then select Choose Connection

excel 2010 measure connection

Now click on Power Pivot Data and select Open

2010 - Existing Connections

Measure addition

In the 2016 version of Microsoft Excel

Right click on the Sales Table and select Add Measure

Measures 06

Alternatively, from the Power Pivot tab, click on measures and then click on New measures

2016 - Add Measure

For the 2013 version of Microsoft Excel, choose the Power Pivot tab, click on Calculated Fields and then select new calculated Field.

2013 - Add Measure

In the 2010 version of Microsoft Excel

Click on Add New Measure after right clicking the sales table

2010 - Add Measure

Alternatively, click on a cell in the Pivot Table, go to the Power Pivot tab and select New measure

new measure excel 2010

Our 1st measure is created here

You can give Total Sales or any other name as the name for the measure

Type =SUM as the formula and select the sum option from the options

sum

This will give you the helper for formula where you can choose Sales[Sales Amount] and then close bracket.

sales amount

Use currency for the category and select OK

Measures 07

Put the created Measure in the area for Values

Measures 08

The created Measure can now be used in the Pivot Table

Measures 09