skip to Main Content

 
image

“When Eric Hutton Talks, People Listen”
(Click for the Vintage TV Commercial on YouTube)

First Guest Post From Eric Hutton

Eric Hutton has been emailing back and forth with me for awhile now but I finally convinced him to write a guest post.  Perfect timing since he was just quoted in the press earlier this week.  So without further ado, the celebrity known as Eric Hutton…

Organizing your Measures with a “Dummy” Table

I have the addition of the “Measure Grid” (aka “Calculation Area”) in V2 to be of mixed benefit. Yes, it is great to have access to your measures from within the PowerPivot Window, but the manner that they did so is so very… messy.

I like to keep my spreadsheets hyper-organized and clean, because often I have to pass them off to someone else who has to be able to understand what I am doing. So I have come up with a workflow to take advantage of the measure grid while retaining a logic to the location of my measures.

The messiness comes from the fact that you can place any measure in the measure grid of any table, but this also provides us the solution for organizing things up.

In the tradition of this blog, let’s take up an example.

We have a little consulting firm doing some work for a couple of big clients, and they want to start looking at their income in two ways; cash based, and accrual based. To get the cash data, we want to sum up over the amounts of their cash receipt using the date of receipt, or maybe date of deposit. To get the accruals data, however, we would want to sum up the values over the date of invoicing, or maybe the date of work completion. This is all easy-peasey to do with PowerPivot – quite similar to the example in Rob’s book of working with budgets versus actuals – time to roll out USERELATIONSHIP.

I have built-up a fake little dataset for this fictitious firm (using randomly generated numbers for all the value fields) with the following four tables: a customer database, a project database, invoice database, and receipts database. Here is a shot of those tables from the diagram view in PowerPivot.

clip_image002

Note all those relationships to the date table!

[ Note: I love diagram view, but I have found it to be a little buggy – trying to open it up in a big model with tons of data, tables and relationships has crashed my spreadsheet in the past – so always save before opening it]

So the measures I want to build are quite simple:

Cash Receipts:=SUM(Data_Receipts[Amount])

Accrued Receipts :=calculate(sum(Data_Receipts[Amount]),USERELATIONSHIP(Data_Invoices[Issue Date],Date_Table[Date]))

The trouble is where is a neurotic compulsive neat freak going to put these? Of course this is a fairly simple example, with both measures being strongly related to the receipts table, it would make sense to include these measures in the measure grid of the receipts table. But there are times when you have measures that have strong connections to multiple tables, so where do you place them?

This is not just a matter of being tidy for tidiness sake, you also want the users of your model to be able to easily find and use the correct measures. My [fictitious] clients here might think that such measures relate to their billing activity, so they might go looking for them in the invoices table.

The solution I have found for this problem is a simple cut and paste away: I create new empty tables which are report specific.

Creating an empty table is very simple. Select two empty cells from a column in any excel sheet and cut. Then go to the PowerPivot window and the “paste” button in the Home tab should be available. If you hit the paste button you will get the following “paste preview” dialog box

clip_image003

Paste Preview for Two Empty Cells

Here I give the table an name related to the report I am creating; in this example I will call it “Report_Revenue_Summary”. I then increase the size of the measure grid to its maximum, rename the column to relate to the particular measures I am going to place in that column, and then hide the column from the client tools. In the end my new table will look like this:

clip_image004

A Table Dedicated to a Particular Report

With this approach, you can go one step further down the path of Rob’s personal rule “I never, ever, EVER create implicit measures!” – you can make it so that implicit measures CAN’T be created by hiding all your data tables from the client tools. This also prevents anyone from trying to organize the data with the wrong field (i.e. using the date field from the receipts table).

So here’s how my PowerPivot Table would look.

clip_image006

Simple Measure Organizing Example

The only trouble with this approach? You have to get used to ignoring that “Relationship may be needed” warning box.

There are other actions I take to keep things organized, one of which may have noticed is to use prefixes on my tables to group them by type (reports, data, etc). Another thing I will do is that if I have a report that has a number of measures which should be displayed in a specific order, then I use prefixes in their names so that they show up in the field list appropriately.

So, for example, say that my client here comes back to me and says they want to see how the accrued collections relate to the cash collections: when is the cash being accrued, and when are the accruals being cashed. This is a simple extension of the above two measures.

First thing I would do is go back and add a new empty column, simply to start organizing my measures in the measure grid; I’m going to have one column for my cash measures, and one for my accruals measures. To do this, just right click on the “Add Column” header to the right of your current empty column and rename the new column. Then right click on the header of the new column again to hide it from the client tools.

To get a measure of what year (or other period) the cash collections came from is a simple matter of filtering the cash receipts data using values from the invoice table. Such as:

Collections for 2006:=CALCULATE([Cash Receipts], YEAR(Data_Invoices[Issue Date])=2006)

This is then repeated for all the years. [note: if there were previous years of operations, but we were only interested in starting our report for 2006, then this formula would be for Year(X)<=2006)

While on the accruals side we have:

Collections in 2006:=CALCULATE([Accrued Receipts],YEAR(Data_Receipts[Date of Receipt])=2006).

Leaving things like this, in our Pivot fields, we would have a somewhat jumbled array of measures:

clip_image007

Huh?

To make it clear how these measures should be presented then, I use prefixes, like so:

clip_image008

Ah, much better.

Using these prefixes also makes it easier to create dependent measures. When I’m creating new measure which depend on other measures, I generally finder it easier and quicker to pull-up the auto-insert value much faster by typing in their prefix code than by typing the text description.

And, of course, you can remove those pre-fixes for display in your reports:

clip_image010

2008 was a good year for them!

clip_image012

2012 Sucked! Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 23 Comments
  1. Great post! I’ve been using the dummy table trick for ‘years’ – makes a massive difference in terms of making models you hand over to other people more robust and presentable. I also prefix my tables with a number so they appear in the order of my choice.

    Unfortunately from my brief experience with the preview this trick doesn’t work in Excel 2013 (not that anybody here will be buying it in the short term).

    1. I haven’t been playing with 2013 – at first because of the backwards compatibility issue, and now because of the issue with it only be selectively available – so I hadn’t seen the issue there.

      Is it just blocking uploading of a blank table? If so… just load up a dummy table that has one cell with a zero in it.

      1. I was half right!

        It allows you to create the blank table however it groups all your blank tables at the top in the of the field list whereas in my 2010 models I start all my table names with a number so the line up in the order my choosing, dimensions at the top!

  2. “You have to get used to ignoring that “Relationship may be needed” warning box. ”

    You can disable the Relationship Detection button to take care of this issue.

    1. Right, good point. I’m neurotic though and I’m always worried that I will forget I have turned off some warning in Excel and then think that since I’m not getting a warning when I could be everything must be alright… (this is something that typically happens to me when I turn off the “inconsistent formula” error). I prefer to see warning knowing I can ignore them, rather than not seeing warning and assuming everything must be ok.

  3. Brilliant, a colleague of mine just started building a model that will require quite a bit of measures. I’m keen to share this trick with him. Thank you.

    1. Some models require a lot of measures, so creating several dummy tables.
      is a a good way to group them.

      Also, I also create a lot!!! of test measures, (usually called measure 1, measure 2….). Now I put them in a dummy table, that I can easily delete after I have found one that gives the right result.

      The same is true for intermediate measures. Creating a separate table for these allows to hide them in the final model and they do not clutter the field list while creating it.

  4. A quick postscript to this guest-post of mine, if anyone is still stumbling across it. I have found that this method can have some quirks in Excel 2013; many times I have found that PowerPivot tries to retain a connection for a linked table, even when I have told it to delete the connection. This never happened in Excel 2010.

    … just one of many irritating interface quirks I have found were introduced in Excel 2013

  5. This best practice in addition to properly organizing your Measures has another important added value.
    It keeps your measures SAFE for ever.
    If for any reason your linked table cannot be refreshed anymore and you have to rebuild it in your model, all the measures stored in this table will be lost.
    Would be nice to be able to do the same with calculated columns; to detach them form the original table 😀

    1. Absolutely- this has become a key advantage for me for continuing this practice with the advent of the very powerful and awesome tool PowerQuery. I love everything about PowerQuery EXCEPT for the fact that certain changes force you to trash your data table in PowerPivot and reload, resulting in you losing any measures created on that table.

      While this technique of using a dummy linked table works to preserve your measures, it can’t help for calculated columns. So I have also adopted the practice of creating a text file where I save all my calculated columns for a given data table – if you’re using PowerQuery, you need to do this too!

  6. I have used the same approach with great success, until …

    When I double-click on a calculation in a pivot table, I get a list of the underlying data.
    When I put my calculations in a dummy table, I lose this functionality.

  7. A quick update for this post…

    If you’re using PowerQuery, you can quickly create a Measures Table like this with the following M code:

    let
    BlankTable = Table.FromValue(“”),
    #”Renamed Columns” = Table.RenameColumns(BlankTable,{{“Value”, “Measures_A”}}),
    #”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each ([Measures_A] “”))
    in
    #”Filtered Rows”

    If you’re working in Analysis Services, and can’t use PowerQuery (one day perhaps?) and are bringing in all your data from an SQL Server, you can also quickly setup a blank measures table by going through the steps to import a table from your SQL Server Database, but choose to do so by writing a query – then use the following query

    SELECT DISTINCT NULL AS Measures FROM [Any table in your database]

    Cheers!
    Eric

  8. I followed this practice but there is a huge problem. If I doble click any cell of those measures, it opens this empty fiction table instead of the details requested.

    I don’t know now how to move my measures back to the normal tables. Thanks, Rob! I always follow your posts…

    Cheers,
    Gerónimo

  9. Hi, thank you for this point, but i have another to do it.
    You can use power query to have a blank table that you will use for your measure.
    The command is presented like this:
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i44FAA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1″, type text}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Column1″})
    in
    #”Removed Columns”

  10. Is there a problem with hosting primary measures used in a power pivot table in a Lookup/Index Table?

    (by ‘primary’ measures I mean aggregate measures of data table-specific measures; for example 5 data tables and a Dollar Sales measure for each, this would be the SUM of those 5 $ sales measures)

  11. Was just rereading this article & saw my old question. LOL! This was clearly asked before I got wise to Power Query’s ability to combine data from folder. Rookie mistake. Please disregard.

Leave a Comment or Question