BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Posted by Andy Brown on 11 March 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Using disconnected slicers for exchange rates

A question from a course I've just given (thanks, Anne!) - how to achieve the following?

When you select an exchange rate, PowerPivot recalculates the pivot table to use it.

Of course, if you choose more than one rate (or indeed, none at all), you want to see something like this:

If you have more than one rate selected, show zeros (or blanks).

Since it's a nice little problem in PowerPivot, I thought I'd give one possible answer. You can download the file I've used here.

## The tables involved

The workbook I've used contains two tables (I've used linked Excel tables):

The two tables in my model.

The thing to realise is that there is no relationship between these two tables! You'll only ever have one currency rate selected at a time, so what we'll do is use this rate in our calculations.

## The measure which does the work

I'm sure there are other ways to solve this, but here's mine:

The measure sums the price in local currency divided by the exchange rate, but only if there's exactly one exchange rate selected in the pivot table.

Here's the measure:

Local:=IF(

COUNTROWS(VALUES(Currencies[Currency]))=1,

SUMX(

Purchases,

[Price in pounds]/MAX(Currencies[Rate])),

0

)

Here's what this does:

- Looks at the currently selected values for the currencies, taken from the choices the user has made in the slicer.
- Counts how many values there are, and returns 0 if either there aren't any currencies selected, or the user has selected more than one.
- If a user has selected exactly one currency, the measure finds the maximum currency rate (because there only is one, this will give the rate for the currency selected).
- The measure then sums the price in pounds divided by the currency rate to use.

It helps to understand this to realise that we could have taken the minimum, sum or average of the currency rate instead. If you know you only have one value, its minimum, maximum, sum and average are all the same!

## Finally ...

One downside of this solution is that it leaves this messy box visible in the pivot table fields list:

The yellow box about relationships is annoying! It's there because we have two tables in our data model which have no link between them, but that's by design!

Sadly, there doesn't seem to be any way in Excel 2013 onwards to remove this yellow warning.