Measures

This is the 1st post on Measures and the 6th post of the Intro to Power Pivot series.  Jump to the 1st post of Power Pivot series here.

Measures allow the creation of custom formulas to harness big data using Power Pivot. Measures use a formula language called Data Analysis Expressions (DAX). Many DAX formulas are similar to native Excel, such as SUM, but they extract the value from a table, column, or subset of your data, instead of referencing cell ranges, and they can be used across your Data Model.

---

Writing a Measure

Start with the workbook created from the last exercise, 2 Data Tables on 1 Pivot Table, or download this file.

The Pivot Table in the file currently has two values named Sum of Sales, which is not helpful.  Using Measures, let's create more meaningful descriptors, such as Budget and Actual.

With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.

In the Measure dialog box, select Budget_Data as the Table name.
Change the Measure name to Budget.
In the formula box, type =SUM(Budget_Data[Sales]).
Change the Category to Number, with 0 decimal places.

The Pivot Table now contains the new Measure "Budget".

---

Learning Curves

Not everyone got that result on the first try.  Let's talk about a few things that could have thrown you a curve, and how to fix them.

Measure not on Pivot Table

If the Budget Measure is not on the Pivot Table at all, it's because the cursor was not in the Pivot Table when New Measure was selected from the Power Pivot ribbon.  Go to the Pivot Table Fields List, and locate the fx Budget checkbox.  Check the box, and the new Measure is added to the Pivot.

Measure in different section of Fields List

If the new Measure is not listed under Budget_Data, in the Fields List search box, type Budget.  Check the box to add the Measure to the Pivot.  This occurs when the Table Name used in the Measure was something other than Budget_Data.

Measure Name or Formatting is incorrect

If the Measure is on the Pivot Table, but it's not named Budget or formatted with a comma and no decimals, then the Measure needs to be modified by reopening the Measure dialog box.  On the Power Pivot menu, select Measures, Manage Measures and modify to match the image above.

---

In this lesson, you learned:

• What a Measure is;
• How to write a Measure; and,
• Troubleshooting for common Measure problems. 

SOLUTION


Prior post: 2 Data Tables on 1 Pivot Table
Next post: Measures In-depth