Plan-fact analysis in a pivot table with Power Pivot and Power Query

Contents

Formulation of the problem

In the initial data we have two tables. Modest in design, large in size, but easy to use table with actual sales values, downloaded from some accounting system:

And a “beautiful” table with planned monthly indicators from the management:

Task: somehow combine both tables into one in order to visually display the implementation of the plan for each product, region, month, quarter, etc.

Required disclaimer

You can, of course, not strain, and solve this matter in the usual way “on the forehead.” Those. with 144 SUMIFS functions (SUMIFS) calculate the total sales for each month, product and city, and then manually calculate the percentage of the plan completed using another 144 formulas.

Then mentally howl when the boss says that he wanted to see the dynamics by quarters, and not by months. And it is better in rubles, not as a percentage. And it is better to arrange the cities in columns, and the months in rows. And don’t whine, you have the whole night ahead, so that it is ready by morning.

And in our example, there are only 3 cities and 4 products. What if there are more?

Let’s better go the other way – a little more complicated, but much more flexible and convenient in the future.

What do we do

I think no one will argue that the most convenient, flexible and powerful tool for data analysis in Microsoft Excel are pivot tables. So, ideally, we should reduce the solution of our problem to them.

But how to combine two of our original tables in one summary? A flat table of sales by day and a three-dimensional table of planned values with details by month? Here 2 must-have add-ins for Excel will help us:

Power Query – built into Excel, starting from the 2016 version, for earlier Excel 2010-2013 it can be downloaded for free from the Microsoft website.
PowerPivot – Since 2013, it has been included in most (but not all, unfortunately) Microsoft Office packages. For Excel 2010 (but not for newer versions!) Download for free, again, from the Microsoft website.

Let’s go step by step…

Step 1. Adding join lookup tables

Linking directly our original fact and plan tables, unfortunately, will not work. Neither Power Pivot nor, moreover, Excel does not yet support many-to-many relationships (many-to-many), which means that there may be duplicates in the source tables (and this is just our case – the names of goods and cities appear more than once in each table).

Therefore, we need to create “crutches” – intermediate lookup tables with unique values uXNUMXbuXNUMXbof goods, cities and dates, which we will use to create one-to-many relationships (one-to-many), which Power Pivot can do with a bang :

To create a date table, it is convenient to use the command Home — Fill — Progression (Home — Fill — Progression):

Step 2: Make All Tables Smart and Give Them Names

To load tables into Power Pivot, they must be “smart” (dynamic). To do this, for each table, do the following:

Select any table cell
Press keyboard shortcut Ctrl+T or choose Home – Format as a table (Home — Format as Table).
In the window that opens, check the correctness of the range selection (especially for the plan table!) And whether the checkbox is enabled Table with headers (My table has headers) and click OK.
On the Advanced tab Constructor (Design) in the upper left corner we give the table a meaningful name instead of the standard faceless ones Table 1,2,3…

I named our tables accordingly:

tablProdaji
tablProducts
tableGeography
tabCalendar
tablePlan

Step 3. Load the first 4 tables into Power Pivot

We have the first four tables in the correct form, so we can safely load them into the Data Model – the memory area that Power Pivot operates with. We connect our add-on through File – Options – Add-ins – COM Add-ins – Go (File — Options — Add-ins — COM Add-ins — Go) and make sure that the Power Pivot tab appears on the ribbon.

Now, in turn, for each of the first four tables, setting the active cell in it, click on the button Add to Data Model (Add to Data Model):

In older versions, this button was called Related table (Linked table).

As a result, all our tables should be loaded into the Power Pivot window that opens on separate tabs:

Step 4. Bringing to mind the plan table

Before loading a table with planned values into the Power Pivot Data Model, it must first be straightened: remove empty rows and totals in it, expand it into a flat one, fill empty cells in the first column with cities, etc. The simplest and easiest way to do this is with the Power Query add-in.

First, load the table with plans into the Power Query editor using the button From Table/Range (From Table/Range) tab Data (Date) or on the tab Power Query (if you have an older version of Excel 2010-2013 and installed Power Query as a separate add-in):

Then, in the Power Query window that opens, do the following:

1. Delete all empty strings with null through Home — Delete lines — Delete empty lines (Home — Remove rows — Remove empty rows).

2. Delete rows with totals by unchecking the corresponding checkbox in the filter by column Product.

3. Remove unwanted last column TOTALby right-clicking on its title – Remove (Remove).

4. Fill in the empty cells in the first column with the names of cities from higher cells by clicking on the column heading City right and choosing Fill – Down (Fill — Down).

5. We expand 12 columns-months into two: the name of the month and its value. To do this, select the first two columns Gorace и Товар (while holding the key Ctrl), right-click on their title and select the command Uncollapse other columns (Unpivot Other Columns).

6. To convert the textual names of months into normal dates, we go to the trick:

We add one before the dates with a space using the command Transform – Format – Add Prefix (Transform — Format — Add prefix)
Similarly, we add after the dates of 2019 through Transform – Format – Add Suffix (Transform — Format — Add suffix)
Now that the text in this column has become much more like a date, we convert all its contents to dates using the type dropdown in the column header:

7. Column Attribute rename to date (double click on the column heading).

8. In order not to confuse the original plan table with the transformed table, change the name of the query to tablePlan2 in the right pane of Power Query (later this will be the name of the table in Power Pivot).

9. We upload the finished table to the Power Pivot Data Model using the commands Home — Close and Load — Close and Load in… (Home — Close&Load — Close&Load to…) and then select the option in the next window Just create a connection (Only create connection) plus, most importantly (!), turn on the checkbox Add this data to the data model (Add this data to Data Model):

After that, our last table tablePlan should load into the Power Pivot window.

Step 5 Linking Tables

Now it’s time to perform one of the most important actions – to link all the tables we have into a single model, so that later we can build a summary for the entire model, as if it were one table.

For linking in the Power Pivot window, it is better to switch to chart mode using the button Home – Chart View (Home — Diagram View) or icon Diagram (Diagram) in the lower right corner of the window. Rectangular table boxes can be dragged by the title bar and expanded in any way you like.

The connection is very simple: we grab a column in one of the connecting tables with the mouse (tablProducts, tableMap, tableThe calendar), drag and drop on the corresponding column in the tables tablProdaji и tabPlan2:

Main principle: pull from reference tables (Products, Geography, Calendar) to fact and plan tables. We make 6 links – each directory should be connected with two links to the plan and sales tables. The end result should look like this:

Pay special attention to the position of ones and asterisks at the ends of the links – these are exactly the same one-to-many links, where the asterisk denotes many occurrences of the same element, and the one denotes uniqueness.

If everything worked out, then save the file and exhale – the job is almost done.

Step 6. Building a summary

Now, based on the created data model, you can build a summary – for this, in the Power Pivot window, select the command Home — Pivot Table — Pivot Table (Home — Pivot table — Pivot table). We will automatically return to Excel, where we will see the familiar panel for building a pivot table on the right side of the screen, but all tables will be visible in it, and not just the current one (as usual):

Now we can, in a familiar way, drag the fields we need from the tables into the pivot table area with the mouse.

The main principles here are:

In the area of rows, columns and filter, you can drop only fields from lookup tables (tabGeography, tabCalendar, tabProducts).
In the area of values where calculations are taking place, you can throw only fields from the fact and plan tables (tableProdaji, tablePlan2)

For example, you can put it like this:

In order for the dates to go through the columns not in increments of one day, but larger – click on any date in the summary with the right mouse button and select the command Group by (Group by), and then whatever grouping level you want:

As a result, you should get something very similar to what we need:

Step 7: Add Calculation Measures

Measures are, simply speaking, formulas inside summary ones. In fact, when we drag any field with the mouse (for example, Revenue) into the pivot table value area, then an implicit measure is created “under the hood” – something like:

Amount in the Revenue field := SUM(tableSales[Revenue])

But we cannot control the process of creating implicit measures – Excel itself decides how to call it, which function (SUM or COUNT) to use, etc. Therefore, it is better to create explicit measures for the summary one yourself – in this case, we will be able to control all their parameters.

To do this, on the tab PowerPivot choose a team Measures – Create Measure (Measure — New measure) and in the window that opens, set:

Here:

Table name – a place to store the measure (you can choose any table – it does not matter).
Measure name – come up with and enter any convenient name (possible in ).
Description – optional.
Formula – enter the formula by which the measure will be calculated. You can use functions from the built-in DAX language in Power Pivot (button fx).
Check Formula – checks your formula for errors and gives recommendations for correcting them.
In the lower part of the window, you can immediately set the number format for the measure, so that later you do not have to set it up 100 times in the summary (as is the case with ordinary implicit measures).

We repeat the process two more times:

Create a measure with a name Fact with formula =SUM(‘tableSales'[Revenue]) and a number format without cents and with a separator.
Create a measure Deviation, which uses the two previous measures created by the formula =[Fact]/[Plan]-1 and percentage format

The added measures will appear in the right panel of the pivot table with a characteristic icon:

Now they can be safely thrown with the mouse into our summary and plan-fact analysis can be performed in any sections in a matter of seconds:

All created beauty is updated (Power Pivot data model, Power Query query and the pivot itself) with one movement – on the tab Data (Date) using the button Refresh all (Refresh All) or keyboard shortcuts Ctrl+Alt+F5.

Possible problems and solutions

In the process of implementation, you can run into several typical “rake”:

Appear strange errors in Power Pivot or alone Power Pivot tab disappears unexpectedly from Excel – disable the add-in, restart Excel, and re-enable it (see Step 3). Usually helps.
Unable to create connection – check if there are any repetitions in the directories. The columns used for linking should not have (in reference tables) duplicates – this is a strict requirement of Power Pivot.
Some strange results are obtained in the summary – check 1) whether you have configured the links correctly 2) whether you use the same fields for the summary (only fields from directories can lie in the rows, columns and filter area).

If there are any other difficulties – write in the comments.

In any case, it’s worth a try – having created such an updated analytical system once, you can enjoy it for a long time in the future 🙂

What is Power Query, Power Pivot and Power BI and why do they need an Excel user
Pivot table for several data ranges at once
Create a database in Excel using Power Pivot