“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.
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:
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
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:
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.
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:
To make it clear how these measures should be presented then, I use prefixes, like so:
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:
2008 was a good year for them!