Dynamic calculation of Multiple Currency via slicer

Anything related to PowerPivot and DAX Formuale
lalaisgongon
Posts: 3
Joined: Wed Jan 31, 2018 12:36 pm

Dynamic calculation of Multiple Currency via slicer

Hi there,

Could I get the help from the experts here!
I have an MNC selling apples in 3 countries, USA, Singapore and Australia.

The Firm is US base and have to see their sales in USD
There have 3 fixed exchange rate over the course of the year, 1 Jan/ 1 Jun / 1 Dec
Looking back at the last year, i want to table to change the rate dynamically as i select the slicer.

Currently, the way i set it up, it works for Singapore only, how can i get it to work for Australia as well?

I have attached the file in this link!

benholmes
Posts: 9
Joined: Tue Jan 30, 2018 9:22 pm

Re: Dynamic calculation of Multiple Currency via slicer

I'm only using Excel 2010 so can't look at your PP data model, however the attached file has a version that works.

First I'd flatten your exchange rate table (I did this with the unpivot function in Power Query) so you've got 3 columns - Rate, Country/Currency and Rate - and add it, your sales table and a Country table to Powerpivot. I'd have the Country table as a lookup table, joined to the other two fact tables.

I'd then have the following measures;
Sales Local:=SUM(Sales[Sales in Local Currency])
ConvRate:=IF(HASONEVALUE(ForexAdj[Currency]),SUM(ForexAdj[Rate])) - using a HASONEVALUE so it doesn't give you a value, which would be meaningless, on the total line if you have it on the PT and
Sales Converted SUMX:=SUMX(Country,[Sales Local]*[ConvRate]) - this needs a SUMX otherwise you wont get a total

Put Country from your Country lookup table (NOT the sales or forex table) on the rows and use your rate field from your forex table on the slicer
Attachments
Example.xlsx