Pinterest Pixel

Using Measures in Power Pivot

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

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.

Key Takeaways

  • Create Custom Calculations ā€“ Measures allow you to perform advanced calculations using DAX (Data Analysis Expressions) that go beyond standard Excel formulas.
  • Efficient Data Processing ā€“ Unlike calculated columns, measures are computed only when used in a Pivot Table, making them more memory-efficient and faster.
  • Dynamic Aggregations ā€“ Measures automatically adjust based on the Pivot Tableā€™s filters and row/column selections, providing flexible and context-aware results.
  • Reusable Across Reports ā€“ Once created, measures can be reused in multiple Pivot Tables, reducing redundancy and ensuring consistency in calculations.
  • Supports Advanced Functions ā€“ DAX measures enable complex operations such as running totals, year-over-year comparisons, and ratio analysis, enhancing data insights.

How to Use Measures in Power Pivot

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

Using Measures in Power Pivot

 

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

Using Measures in Power Pivot

 

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.

Using Measures in Power Pivot

 

In Excel 2010

Go to PowerPivot >Ā Create Linked Table.

Linking Excel Tables in Power Pivot

 

STEP 4:Ā This will open theĀ Power Pivot Window.

TheĀ Sales Table will nowĀ be automatically loaded to the Power Pivot Data Model.

Using Measures in Power Pivot

 

Now Close theĀ Power Pivot Window.Ā 

Using Measures in Power Pivot

 

STEP 5:Ā Go toĀ Insert> Pivot Table.

Using Measures in Power Pivot

 

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.

Using Measures in Power Pivot

 

In Excel 2013

Go to Use External Data Source > Choose a Connection

Using Measures in Power Pivot

 

Now selectĀ Tables > This Workbooks Data Model > Open:

Using Measures in Power Pivot

 

In Excel 2010

Go to Use an External Data Source > Choose Connection:

Using Measures in Power Pivot

 

Now selectĀ PowerPivotĀ Data > Open:

Using Measures in Power Pivot

 

STEP 6:Ā Adding a Measure:

In Excel 2016

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

Using Measures in Power Pivot

 

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

Using Measures in Power Pivot

 

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

Using Measures in Power Pivot

 

In Excel 2010

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

Using Measures in Power Pivot

 

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:

Using Measures in Power Pivot

 

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:

Using Measures in Power Pivot

 

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:

Using Measures in Power Pivot

 

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.

Using Measures in Power Pivot

 

 

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

Using Measures in Power Pivot

 

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

Using Measures in Power Pivot

Frequently Asked Questions:

What is a Measure in Power Pivot?
A Measure is a custom calculation created using DAX (Data Analysis Expressions) that performs aggregations like sum, average, or complex calculations within a Pivot Table. Unlike calculated columns, measures are evaluated only when needed, making them efficient for large datasets.

How do I create a Measure in Power Pivot?
To create a Measure, go to the Power Pivot window, click on the “Home” tab, and select “New Measure.” Alternatively, in an Excel Pivot Table, right-click on the table name in the Fields List, choose “Add Measure,” and enter your DAX formula.

What is the difference between a Measure and a Calculated Column?
A Measure is dynamically calculated based on the Pivot Table’s filters, making it efficient and reusable. A Calculated Column, on the other hand, creates a new column in the data model with static values, increasing memory usage.

Can I use multiple Measures in the same Pivot Table?
Yes, multiple Measures can be added to the same Pivot Table. Each Measure provides its own calculation, and they can be used together to analyze different aspects of the dataset, such as profit margin, total revenue, and average sales.

Why is my Measure not displaying correctly in the Pivot Table?
If a Measure is not working as expected, check the DAX formula for syntax errors, ensure the correct aggregation function is used, and verify that the data model relationships are properly set up. Also, make sure that the Measure is added to the Values area in the Pivot Table.

PIVOT BANNER

If you like this Excel tip, please share it



Using Measures in Power Pivot | MyExcelOnline


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.

See also  Installing Power Pivot in Excel 2010

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME ā€” no strings attached! Even if itā€™s on day 29, you wonā€™t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...