How to Group Measures in Power Pivot

Setting up one or more tables to hold your measures is a very good idea and makes your model much more user friendly.

It can also save significant re-work if you have to re-develop a data table and you stored some of your measures in that table.

Ideally follow these steps BEFORE doing anything else in your Power Pivot model.

1. Copy an empty Excel cell

2. Launch Power Pivot (Manage Data Model)

3. Click in the existing table and click the Paste button. This is actually Paste to New Table so don't worry you won't paste over any existing data

4. Rename the Table as Values (or whatever generic label you like, it could be MyMeasures, Calculations, Finance Metrics, HR Measures, etc...).

Then Click OK

5. Right Click on Column1 and select Hide from Client Tools. This prevents the item Column1 appearing in your list and also magically makes your measures appear at the top of your Pivot Table field list.

6. Now when you go to the Power Pivot menu and create new measure, you can select your "Values" table and they will be nicely grouped together.

Also, back at the start of this article I recommend setting up these tables as your very first action. This way the measures table is always the default table when creating new measures.

7. To save having to do this every time, add a couple of Measures Tables to a template file.

The one downside of a measures table with column1 hidden is you can't right click on that Measures Table in the Pivot Table fields list and add new measure. For that reason I sometimes unhide Column 1 until the end of the process.

Also, a small note, the Right Click add new measure box that appears it subtly different (worse) to than the Power Pivot tab > New Measure box that appears. Intellisense and Tab work better in the 2nd method.

Making your model easy to use should be foremost in your thoughts. This should contribute to that result.

Wyn

My articles

Amazing Excel and Power BI Solutions

www.accessanalytic.com.au

Power BI DESKTOP

Here's he article on how to do it in Power BI Desktop, that inspired me to write this one.