Power Pivot Principles: Disconnected Tables and Exchange Rates

Welcome back to our Power Pivot blog.  Today, we discuss why disconnected tables may be useful in Power Pivot.

In our previous blog, Power Pivot Principles: Calculating with Other Tables, we highlighted how useful connected tables can be when creating measures.  This time, we will cover a useful application of disconnected tables, using them to help calculate values in different currencies.

I’ll begin with creating the following table in Excel; note that the values in column B have to be derived through this formula:

Once this table has been created, highlight it and convert the range into a Table (CTRL + T). The next step is to highlight the entire Table and copy it into Power Pivot. I have named this Table Exchange_Rate. We can do this by going to the ‘Power Pivot’ tab on the Ribbon and selecting the ‘Add to Data Model’ option, viz.

After adding the table to the Data Model, we can create a new PivotTable through Power Pivot with [Sales] by SalesTerritory:

Create a measure called FX:

Add a slicer on the Exchange_Rate Table, on the ‘AUD Per Euro’ column.

Note the fact there are two “tables” called Exchange_Rate – one referring to the Table itself and one to the table loaded into Power Pivot.  We have selected the Power Pivot one in the image (above).

This slicer does not manipulate the PivotTable, as the slicer updates the Table but not the PivotTable – this is because they are not connected!

What’s next? Well, let’s create a new measure:

Now remove the other fields from the PivotTable’s ‘Values’ area so we just have the ‘Sum of SalesAmount’ and ‘Sales in Euros’.

Now, changing the slicer will automatically convert the sales amount into Euros!

That’s it for this week.

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.