skip to Main Content

 
image

Latest Article for CIMA Insight

Some of you know that I’ve been writing a series of articles for the Chartered Institute of Management Accounts, aka CIMA.  Up until this point those articles have either focused on the organizational impact, the “why” of PowerPivot.  Things like how it contrasts with traditional BI and traditional spreadsheets.  How it saves costs and delivers more.

And then I started to introduce basic topics that have already been covered at length here on this blog, just simple how-to-get-started stuff.

But this month is the first time I wrote something for CIMA that I have not really covered here.

The Problem:  60k Sales Rows vs. 2k Budget Rows

PowerPivot Sales Data Very Granular Day Level

Sales Table with 60 Thousand Rows

PowerPivot Budget Table Less Granular Month Level

Budget Table with Two Thousand Rows

The Desired Result:  A Single Unified Report

PowerPivot Report Combining Day Level Actual Sales and Month Level Budget Granularities

The Solution

Diagram of How to Integrate Different Granularities of Data in PowerPivot

Here’s where I have simplified it a bit for the introductory audience.  I likely have a Calendar table in a well-designed model, and that doesn’t appear in the diagram.  I also likely have to solve for more than just Time granularity – the Budget is also likely less granular at the Product level as well.

But to introduce this powerful concept, I think a simple example is best so I kept it clean.

Click here to read the full (short) article and see the details of how I create the linkage between the two tables.

Popular Topic This Month!

Everyone has this on the brain this month it seems Smile

First, in the exact same issue of CIMA, there’s an article about handling Sales vs. Budget using traditional Excel.

And Marco Russo has written one about this same topic in PowerPivot and BISM Tabular.  As usual, his approach is excellent, and quite different from what my more primitive mind conjures.

I didn’t even have to use the Boot Signal:

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 18 Comments
  1. So … this is a subtle introduction to dimensional modeling.. right ? For all those non – believers ;). When exposing your tabular model to “end users” I believe it is the way to go. But wait … exposing the tabluar model to end users means involving “end users” on “different level .. mmm I would love to see a topic on who you “get” people to adopt a ad hoc report model like powerpivot.

    1. Hello Ian! In some sense yes of course – this is the beginning of what OLAP pros would call dimensional modeling. But in another important sense the answer is no – this is not the beginning of Excel pros starting to walk around talking about dimensional modeling, any more than VLOOKUP was their introduction to normalization/denormalization or “left inner joins.” This is, as I think you were suggesting, the beginning of a common-sense understanding and approach to something that has long been available to a more advanced audience.

      Take me for example. I was exposed to OLAP models for years, even working closely with the SSAS team to put integrated features into Excel. But PowerPivot is the first time that *I* have built models like this. So in a very practical sense this is MY intro to dimensional modeling as well.

      Question for you about your question: there is a difference between the user who builds a PowerPivot model/report from the user who consumes it. Which user are you talking about? Or is the mere fact that I make a distinction part of the answer to your question?

      I think a post on this topic is overdue. I’ve been posting about the optimal organizational approach for two years, off and on, but a “tie it all together” post would be a good idea soon.

      1. To answer your question about my question 😉

        I was referring to the user USING the model not to the user building one.

        My biggest struggle was “selling” the dimensional model. After having given several workshops regarding PowerPivot I see people “naturally” adopting the idea behind dimensional modeling, without me explicitly mentioning it.. that for me is the power of PowerPivot .. Pragmatics at work.. oh wait isn’t that name already taken 😛

  2. I created a mini-version of this to try. There is something I’m just not getting about table relationships, because I get bad results and lots of “Need Relationship” messages.

    Exactly how do your tables relationships read?

    Sorry to ask such a basic question – but I bet there are other readers out there who are stuck on the really simple stuff.

    1. Janet,

      Have you created a PeriodId on the Budget table and joined the Budget table to both the Period table on PeriodId and to the Product Sub Category table on Product SubCategory name? That should establish all of the correct relationships.

    2. Janet please do not apologize. I wish I got many MORE questions like yours, because it helps me “tune” what I am saying. I have a very conversational nature and the mostly one-way communication of the blog feels a little strange sometimes – I have no idea if people even find a given post relevant, much less whether I was clear.

      I sent you an email with a couple q’s. Also see Bob’s recent comment as well. When we get to the bottom of this, I will circle back to the blog with our result/answer.

  3. Hi! I have read a LOT about PowerPivot, spent days on it but can not solve my particular problem. Perhpas you can help?

    I want to join two datasets/tables and see different comparisons. Let us say one is ratings minute by minute and the other is a year full of program data. The relationship is made via column where I concatenated a date and the time. For example this very moment would be 1202131415 (yymmddhhmm). When I make relationship, it works but only so far. Each minute from either table will show all the data in this minute. For example:

    1202091000 will include all the data from other table and vice versa. I just don’t get it. Tried also changing formatting to text, general and number. Also tried the “middle” table for relationships. Nothing. How can I solve my problem? I would be so grateful to just get a hint what is going on. Btw, never used powerpivot before, so most likely I am missing something very obvious.

    PS! For testing purposes I work with data from one day only of course.

  4. Hi Rob, This is a great start but it seems in reality we’ve just forced the Sales into a coarser granularity – in other words I’m now locked out of the finer Sales details. Well not really locked out, but the Budget figure is wrong – what I need is for budget to be BLANK() at finer detail levels.

    I am using this as a starting point, but we want to be able to drill into the sales further. In your example, I would need to be able to drill into Mountain Bikes to show specific models, colors, etc. I want the Budget-related cells to be blank when I’m at a lower granularity than it can handle (rather than repeat the $6.5M value).

    A second example would be to filter the pivot table down to a specific period (for instance, Period 1, 2004), then place the Sales Date below Category so I can see daily sales. Again I would want to see the Budget at the lowest level it is capable of going, i.e. Mountain Bikes Period 1 = $500,000, and the rows below at the individual date level would be blank.

    Marco Russo has an example on sqlbi.com (titled Budget and Other Data at Different Granularities in PowerPivot) which is closer to what I’m trying to do – he creates virtual relationships inside the measures – but it breaks down under certain conditions, for instance when there is a budget for an item in a particular period, but no actual sales in that period.

    1. Ian,
      I’m looking into that same issue. I’m fairly new to PowerPivot and would love to see how to solve for this problem. I’m attempting to take our general ledger info along with our AOP and Forecast data to create a budget variance and forecast variance model.

  5. Hello,

    This post helped me a lot. I have a fishing plan which is a plan for each month and then I compare it to real results.

    However I would like to add the third dimension, ship. Is that possible. It would be like you would add a salesperson the budget plan to see if each salesperson is doing its job.

  6. Hello,
    I’ve been using Excel tables/pivots and am wondering if PowerPivot can solve my budget dilemma. The budget figures are produced by FY/Period. Each period has either 4 or 5 weeks, and that is not consistent year to year. (FY 2013, Per 1, 4 weeks; FY2014 Per 1, 5 weeks).
    Sales table data entered by date, How can I break down the period budget amount by week?
    Pivot example would be FY 2014, Per 2, Week 1, Sales vs. Budget.
    I have a FY table: Date, FY, Per, Week.

  7. Hi
    your CIMA article on how to combine actuals & budget helped me a lot to do my data model & would like to know, how to calculate Actual vs budget column in the report

  8. Here is a twist on the granularity I need help with. I have “baseline” Planned resources (think budget) by Day of week. E.g., 10 on Sunday, 12 on Monday, etc. I have actual and “current planned” resources by Date (true date field). How can I link them together so that a “today” slicer can control all 3 data sets. I have a “Day” table joined to the date dimension and “baseline plan”. What else am I missing? A DOW field in the other data sets?

  9. The article behind this link is missing….”Click here to read the full (short) article and see the details of how I create the linkage between the two tables”. Can you provide?

Leave a Comment or Question