, , ,

Sales/Budget: Integrating Data of Different “Grains”


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

The Desired Result:  A Single Unified Report

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:

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog