Simplifying Time Calculations and the User Experience using Disconnected Slicers

Guest Post by Jeff Lingen [LinkedIn]

How does PowerPivot fit in an enterprise BI environment

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 Keeps it Simple Stupid

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.

Simplifying time calculations in PowerPivot using Disconnected Slicers

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

image

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.

Disconnected PowerPivot Table for Time Calculations

“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).

Time Intelligence Formulas using the Greatest Formula in the World

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:

Hide PowerPivot measures from the client pivot table

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:

image

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:

Disconnected PowerPivot Slicers

Lots of SWITCH()’s

19 Responses to Simplifying Time Calculations and the User Experience using Disconnected Slicers

  1. Scott Kennedy says:

    Is this workbook available for download? :)

  2. Jeff Lingen says:

    Thanks for the interest Scott. Try accessing it here: https://skydrive.live.com/redir?resid=58E57887588CE044!309

    • Scott Kennedy says:

      Jeff-

      I have excel 2010…can you save another version that will allow me to open with Excel?

      • Jeff Lingen says:

        It was created in 2010. Maybe try downloading the file first by right clicking on it, rather then opening it in the browser and choosing “open in Excel”.

  3. Andy C says:

    This is very, very cool. Thanks Jeff!

  4. Andrew Sinha says:

    Simply Better!

  5. Siraj Samsudeen says:

    This is brilliant, Jeff! Worthy enough to be included as a best practice in the next version of Rob’s book. Great job and a great idea!

  6. Hassan Murad says:

    Thanks for sharing…great implementation of time intelligence. One question – any reason for including both the Calculation and Comparison dimensions in the same table…personally, i would have created two separate tables for these. Also interested in understanding if this combined master dimension table approach can be applied in other scenarios (e.g. with dim tables related to fact)…thx

    • Jeff Lingen says:

      Only reason I combined the two attributes into a single dimension was for simplicity. I’m a bit of a stickler for eliminating as many things as possible that might confuse an end-user, no reason why they couldn’t be separated out into two dimensions. As far as combining other more traditional dimensions, it might get a bit sticky having to create measures using the USERELATIONSHIP() function. Although I cant really think of a use case that might make sense to combine dimensions.

  7. Oleg G says:

    Jeff, do you see performance impacts of using these disconnected table measures as opposed to “regular” measures? In my case, I had a similar formula with IF’s instead of SWITCH, and the disconnected table measure took 4X as long to refresh as a regular measure would. I ended up abandoning in favor of many tabs in a workbook addressing different time intelligence scenarios.

    • Jeff Lingen says:

      Great question! You’re absolutely right, performance is terrible using a nested IF statements. It wasn’t until PowerPivot v.2 and the addition of the SWITCH function that this technique became useful. I have not had any noticeable performance issues using SWITCH and have implemented far more complex and lengthy statements then this example. Look into switch, its wonderful!

      • Oleg G says:

        I wish I could! My company’s computers are locked into v1 until unforeseeable future upgrade. I will try testing SWITCH() on my home PC in Excel 2013 though.

  8. Aden says:

    Hi Jeff.
    Great post! Start learning!
    By the way, do you have your own blog something?

  9. Mubbasher says:

    Awesome post, really makes things flexible and more dynamic. Thank you for sharing

    • Mubbasher says:

      Seems like there is an extra paranthesis at the end of one statement in section 3:
      ,[COMP_ID] = 3,[YTD]-[YTD-LastYear])

      Also, is there a way to dynamically update the formatting also (so that percentages show properly when others values are being presented with Currency format)?

      • Jeff Lingen says:

        It sure did, thanks for pointing that out!

        Great question about the formatting on the percentages. I wish I had a better method but typically what I do is hide a pivot somewhere in the workbook (our use a Cube formula), connect it to the Comparison Slicer then use conditional formatting based on the value in my hidden pivot. I updated the example file to illustrate this simple approach.

  10. Eric Hutton says:

    “Great question about the formatting on the percentages. I wish I had a better method but typically what I do is hide a pivot somewhere in the workbook (our use a Cube formula), connect it to the Comparison Slicer then use conditional formatting based on the value in my hidden pivot. I updated the example file to illustrate this simple approach.”

    Sounds like great material for another post :-)

Leave a Comment or Question