Guest Post by Jeff Lingen [LinkedIn]
We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.
Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?
Until recently I had generally ignored PowerPivot when it came to developing what some would call traditional “enterprise-level” solutions. I just didn’t know how to implement it. Instead, I’d build great systems with loads of functionality and impressive new features with the hopes that it would provide a whole ton data to wide range of users for a long time. After finishing these projects I was typically left with this underlying frustration and dissatisfaction that I couldn’t explain which was eventually confirmed as these systems slowly would end up forgotten about. You see, traditional enterprise BI solutions are big, powerful, sophisticated – and expensive. But the real trouble with them is they are slow to develop and too often miss the mark in terms of value and user buy-in (something Rob has brilliantly been exploring recently, read this).
Most organizations are in the business of selling things or providing services. Not many that I know of measure their success based on how sophisticated or powerful their reporting tools are. The truth is, success is measured by results. Business people don’t want data or reports or my big powerful “enterprise-level” BI solutions; what they really want is information. They want information that helps them answer business problems quickly and easily so they can move on and do other things that drive results.
PowerPivot has since become my go-to solution not because I figured out how to turn out big, complex enterprise-level tools that last forever using it. Instead I’m realizing how I can turn out lots of small, flexible, dynamic and disposable tools that are focused on answering a single business problem by delivering the insight and information that drive results.
Enough of the Soapboxing, get on with it…
For me, the reason for this evolution is just how flexible and easy it is to create with PowerPivot. Not just spicy DAX formulas and fancy data mash-ups but also simple techniques that encourage end-users to explore and understand their data and remain engaged. The following example is one that has helped to simplify the user’s experience while still delivering powerful insights.
The concept is pretty straight forward. Instead of having a bunch of different measures that do various calculations related to time, you present the user with only one measure and let them easily manipulate it with filters.
Quick Definitions
Goal: I want to be able to control the calculated total based on two dimensions of time
1. Aggregations across time: “Calculation”
Year-to-date, Quarter-to-Date, Current value of the selected time period
2. Differences over time: “Comparison”
Last Year, Net change over last year, Percentage Change (Growth) over Last year, Current value of the selected period
The Year-To-Date total through February 2012 was $89.92
How-To: Disconnected Tables
First construct a new table to control the time calculations, combining all possible combinations of “Calculation” and “Comparison”. Hide the ID columns, making only the labels available to the client.
“Time Calc” table: 12 possible combinations
Next create two “variable” measures to detect what the user has selected.
[CALC_ID] = MIN(‘Time Calc’[CALCID])
[COMP_ID] = MIN(‘Time Calc’[COMPID])
Step 2: Time Intelligence Measures
Create 6 basic time intelligence “variable” measures: Current Year and Last Year for each of the three “Calculation” types (current, QTD and YTD).
There are a few ways to handle Time Intelligence with DAX. I personally prefer the Greatest Formula in the World.
Step 3: Where the magic happens
Well not really magic, just some basic logic to pick the correct “variable” measure to display based on the user’s selections:
Total:=SWITCH( TRUE()
,[CALC_ID] = 1
,SWITCH( TRUE()
,[COMP_ID] = 1,[Current]
,[COMP_ID] = 2,[LastYear]
,[COMP_ID] = 3,[Current]-[LastYear]
,IFERROR(([Current]-[LastYear])
/ABS([LastYear]), BLANK())
)
,[CALC_ID] = 2
,SWITCH( TRUE()
,[COMP_ID] = 1,[QTD]
,[COMP_ID] = 2,[QTD-LastYear]
,[COMP_ID] = 3,[QTD]-[QTD-LastYear]
,IFERROR(([QTD]-[QTD-LastYear])
/ ABS([QTD-LastYear]), BLANK())
)
,[CALC_ID] = 3
,SWITCH( TRUE()
,[COMP_ID] = 1,[YTD]
,[COMP_ID] = 2,[YTD-LastYear]
,[COMP_ID] = 3,[YTD]-[YTD-LastYear]
,IFERROR(([YTD]-[YTD-LastYear])
/ ABS([YTD-LastYear]), BLANK())
)
,BLANK()
)
The SWITCH() function might be one of the greatest things ever, if you’re not familiar with it read this. All this is doing is iterating through the various possibilities first by “Calculation”: [CALC_ID] = 1, 2, 3 or “else” = nothing. Next by “comparison”: [COMP_ID] = 1, 2, 3 or “else” = 4.
Clean up
Hide all the “variable” measures:
This might be the single greatest thing since… SWITCH()?
Some Other Formatting Notes:
Grand Totals don’t really make much sense for a lot of these scenarios, better to hide them.
As with all Disconnected tables, you will get notified that a “Relationship may be Needed”, turn off Relationship Detection or ignore it.
Lastly, because there isn’t a relationship to the fact table, the slicer options may show as inactive. I usually un-check “Visually indicate items with no data” in the Slicer Settings options.
Neat trick but why?
1. Remember my rambling about enterprise BI and where PowerPivot fits? Almost all of our end-users access these workbooks using Excel Services in SharePoint where re-arranging and modifying the pivot table to suite their own specific requirements isn’t possible. I needed a way to let users work with the data without me needing to publish 100 different versions for 100 user’s different areas of focus.
2. The Time Calcs dimension doesn’t have to just be slicers:
Hey, Insight!
3. This technique isn’t limited to just Time Intelligence. I’ve expanded this technique for all sorts of other applications. From simply choosing from a list of possible measures to combining those measures with different periods of time and ways of normalizing totals:
Lots of SWITCH()’s
