Power Pivot Principles: Calculating with Other Tables

Welcome back to our Power Pivot blog.  Today, we discuss how the CALCULATE function can benefit from connected tables.

In our previous blog, we created relationships between all of the imported tables in Power Pivot; with these links we can now create measures that utilise these links.

If you do not have the working file you may download a copy here. 

Let’s create the measure ‘Promotion Sales’ which we envisage to calculate the total amount of sales that were made under the promotion key of ‘14’:

The CALCULATE function is not limited to using filters from the same table as it’s expression, it can accept filters from other tables as well!

To show this, let’s insert our new measure into our PivotTable:

Thanks to linked tables we can now see how much sales were made with promotions throughout the year.

Aren’t connected tables great?

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.