ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #386 - Power Pivot: RELATEDTABLE

Author: David Lyford-Smith

Published: 23 Mar 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Developer level post in which we are returning to Power Pivot for a look at how to use a clever trick to expand the range of measures you can calculate. For a refresher on measures, check out TOTW #369.

The data, and the problem

We have this table of sales over time:

Excel screenshot

We also have the sales reps who are active in each country:

Excel screenshot

What we want is the total sales for each sales rep – including all the countries they are active in. Unlike previous situations we’ve examined, where we had categories which were expanded into further detail, here our two categories – sales reps and sales – both cover multiple countries, and neither ties to the country list uniquely – for example Adam and Isabella are both active in Spain. We can’t directly relate the two country fields as both contain duplicates, so there’s no way for Power Pivot to connect the two.

Solving the problem

First, we will need to create a very simple country list table:

Excel screenshot

We then import all three tables into Power Pivot, and create connections between them:

Excel screenshot

We can now create a simple SUMX measure to get our total sales measure:

Excel screenshot

This measure however still won’t work if we try to create a Pivot with the Sales Reps and the Total Sales:

Excel screenshot

Power Pivot isn’t able to figure out what we want and so the filter context in the Pivot is not applied – all the reps show the full amount.

To do this correctly, we need to use a CALCULATE function to calculate our TotalSales measure over the rows of the SalesReps table. To do this, we use the RELATEDTABLE function, which shows which table we want to use for the connection:

Excel screenshot

This takes our TotalSales measure, which is tied to the Sales table, and changes up the calculation context to calculate over the SalesReps table instead.  Of course without any context in Power Pivot, the total is the same figure again – but the difference comes when we create a PivotTable on this new measure:

Excel screenshot

Now we are able to get a total for each sales rep! Note that the grand total doesn’t agree to the total of the individual figures any more, as we are double-counting some figures where a country has more than one active sales rep. But this approach can be used for any kind of situation where there isn’t a straightforward one-to-one or one-to-many relationship between the values in our tables.

Check out the data and the resulting Pivot in the attached workbook.

You may also like

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.