Creating Measure Tables in Power Pivot

In this video, I'll show you how to create measure tables in Power Pivot. Measure tables allow you to take measures that are scattered throughout the tables in your data model and organise them into dedicated tables of their own.

Rather than hunting through multiple tables to find the measures you need, it's immediately obvious where they are. This saves time, improves the usability of your model, and reduces the risk of duplicate measures being created.

Before I show you how to create measure tables, I want to briefly explain why we use them and the problems we can encounter if we don't.

When we create measures, they must be created within tables. Typically, they end up being created within tables that contain data. For example, here we have the Products table, which contains product data, and then we have a number of measures within that same table.

The same thing happens in the Customers table. We have customer data and then more measures.

If I switch to Diagram View, we can see the data model. This file is actually taken from our Power Query and Power Pivot Automating Reporting course.

In the course, we have 30 measures. Normally, they're neatly organised into measure tables, but for the purposes of this demonstration, I've scattered those 30 measures throughout 7 different tables in the data model.

You can see the sigma symbols indicating measures. These measures are living inside tables that don't really have anything in common with them.

This creates a couple of problems.

Firstly, we can lose track of which measures we've already created because they're difficult to locate. As a result, we may accidentally create duplicate measures.

Secondly, it creates a usability problem, and I can demonstrate that by switching back to the Excel workbook.

For reports like this one, where I'm referencing measures using CUBE functions, it doesn't really matter which table the measures sit in. I can simply change filters and the report updates. The same applies to this P&L report.

However, if we look at this PivotTable and open the field list, we can see that several measures are being used. These measures live in different tables throughout the model.

The tables are shaded because they contain measures that are currently being used in this PivotTable. If I expand one of the tables, we can see a measure. If I scroll further down, there's another measure in a different table.

You can imagine that if measures are scattered throughout the model, finding the one you need becomes a process of hunting through multiple tables.

That's not ideal.

What we really want is something like this.

In this version of the file, all of the measures have been grouped into measure tables. You can see those measure tables at the top of the PivotTable field list, indicated by the sigma symbol.

If I expand them, we can see the measures neatly grouped by category.

For example, we have:

• P&L Measures
• Sales Measures

This makes it much easier to find and use the measures we're looking for.

If I show the same file in Diagram View within Power Pivot, you can see the measure tables at the top of the model. Notice that they aren't connected to the data model itself. There are no relationships because measure tables never need to participate in the model relationships.

Creating a Measure Table

Let's see how to create one.

In an Excel worksheet, select a blank cell and press Ctrl+C to copy it.

Next, open Power Pivot and select Paste from the Home tab.

The Paste Preview window appears. Normally, this feature is used to paste a table of data into Power Pivot. However, because we've only copied a single blank cell, we're effectively creating an empty table.

All we really need to do is provide a table name.

I'll call it Our Measure Table and click OK.

The new table is created and contains nothing except a blank column.

Moving Existing Measures

If you already have measures scattered throughout your model, you'll need to move them into the measure table.

One way to do this is in Data View.

For example, I can go to the Products table, select a measure, highlight the formula in the formula bar, and press Ctrl+X to cut it.

This temporarily removes the measure from the model.

I can then switch to the measure table, select the formula area, press Ctrl+V to paste the measure, and press Enter.

The measure now belongs to the measure table. (Note: it’s not yet a true measure table…that’s coming later!)

If I switch to Diagram View, I can see the measure table containing the blank column and the measure I've just moved.

A Faster Method

A quicker way to move measures is to use Manage Measures.

From Excel, go to:

Power Pivot → Measures → Manage Measures

Select a measure and click Edit.

From the Table Name drop-down, select Our Measure Table and click OK.

You can repeat this process for additional measures.

If I go back to the PivotTable field list, we can already see the measure table containing the measures that have been moved.

However, it isn't yet a true measure table because it doesn't have the sigma icon.

Converting It into a True Measure Table

To do that, return to Power Pivot and switch to Data View.

Select the blank column in the measure table, right-click it, and choose Hide from Client Tools.

If I return to Diagram View, the column now appears greyed out.

Back in Excel, the measure table immediately moves to the top of the PivotTable field list and displays the sigma icon.

At this point, it has become a proper measure table.

Essentially, all we've done is hide the blank column so that the table contains only measures.

Best Practice

You can continue moving existing measures into the table, but my recommendation is to create measure tables before you start building measures.

Create one or more measure tables at the beginning of your project and then create measures directly inside them.

When creating a new measure, simply select the appropriate measure table first and create the measure there.

That way, you avoid the need to move measures later and keep your model organised from the start.

Thanks for Watching

That's how you create measure tables in Power Pivot.

Thank you for watching. I hope you found this useful.

And, as I mentioned earlier, if you'd like to learn more about the techniques used in this file, they're covered in our Power Query and Power Pivot Automating Reporting course.