Dashboard to compare different companies with different currencies

I have a table with multiple companies.

Shop Currency
S3 kr
S4 £

And a table with the exchange rate

Currency Exchange rate
£ 1.17100000000
kr 0.09730000000

When using a formula like this

YearRevenue = SUM(Shop[Revenue]) * SUM(Currency[Exchange rate])

Logically what it now does is add all revenues in 1 pile and add all the conversions rate in 1 pile.

So what happens is all the revenue will be multiplied by 2.2683

I want this to be calculated for each shop individually and sum up at the end.

I think I have 2 options:

  1. Is to add a column with the calculated revenue based on the exchange rate so I don’t have to use a formula.
  2. A formula which I have no idea on how to aproach.

>Solution :

You can use SUMX, your expression would be like this:

YearRevenue = SUMX(Shop,Shop[Revnue]*RELATED('Currency'[Exchange rate]))

Make sure that your 2 tables are linked with a many to one relationship on Currency column.

Leave a Reply