skip to Main Content

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


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:

    ,[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())

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:


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

This Post Has 27 Comments
      1. 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”.

  1. 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!

  2. 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

    1. 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.

  3. 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.

    1. 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!

      1. 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.

    1. 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)?

      1. 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.

  4. “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 🙂

  5. Awesome trick Jeff. I’m going to use this all over now :).

    One question.. I have a slightly I’d think simpler requirement. I have several snapshots of financial data e.g. Prior Year, Plan, Previous Forecast, Current Forecast etc all in one table with a column called Snapshot Type that helps differentiate between each of these snapshots.

    I want to provide user option to select which 2 snapshots to compare. Based on whatever the user selects, I will show the Base value and the Variance against the 2nd option.
    e.g. Base Revenue:=CALCULATE([Revenue],Financials[Snapshot Type]=””)

    Compare To Revenue:=CALCULATE([Revenue],Financials[Snapshot Type]=””)

    Variance Revenue:= [Base Revenue] – [Variance Revenue]

    I was able to use your technique and implement it using SWITCH but wondering if there is an easier way to achieve it given my end formula is the same and just the filter changes?

    Basically, some way of translating whatever the user selects into the CALCULATE formula filter.

    There is an way explained here ->

    However, this only works in Excel 2013 and I need a way to do it in Excel 2010.

  6. This is fantastic Jeff!! Do you have an example of the Rolling weeks filters under number 3 above? Thanks a million!!!

  7. Hi guys,

    Looks like a great solution! I’m actually developing something like this at the moment in Tabular. However, I don’t have 1 measure I want to perform time calculations on, but 20. And I don’t fancy writing 20 measures multiplied by 20 time calculation measures (400 DAX queries). Is there any way you know to also make the base measure (like gross sales, quantity, net sales, discount amount, etc) variable? Or otherwise, make these calculations dependent on the setting of the time calculation dimension? So have a measure Net Sales := Calculate(Sum(sales), dynamic filter based on time calc setting)?

    1. Thanks Bas! You’re right, 400+ measures would not be very efficient and extremely hard to maintain. Unfortunately, there isn’t really a way to create a “dynamic” measure like I think you are suggesting without some tricky data transformation at the source before bringing it into the model. In your case I would create a dimension/lookup table that lists all the calculations (measures) that you will need (gross sales, quantity, net sales, ect.), then transform your data so you have the appropriate values on rows instead of across columns. This way you can join each row to the appropriate measure in the lookup table. After, write a single measure with the time intelligence technique shown here – you select the measure using the dimension – “dynamic” measure. This is actually shown in the blog post but not explained, see the image of the slicers at the very end of the post.

      1. Thank you for your answer Jeff. For now, we have decided to leave it, but I will definitely bookmark this suggesting if I ever were to run into this problem again. Thanks!

  8. Hi, does this work in Power View? I am getting an error when trying to create the slicer “Not showing data because its not clear how these fields are related”… Thank you!

  9. sorry that turned out to be a silly question, I was trying to create the slicer inside a tile container – once I placed outside of the tile container now it actually works !
    thank you, this is the best thing since the investion of the wheel!!

Leave a Reply

Your email address will not be published. Required fields are marked *