I have a table with multiple companies.
| Shop | Currency |
|---|---|
| S1 | € |
| S2 | € |
| S3 | kr |
| S4 | £ |
| S5 | € |
And a table with the exchange rate
| Currency | Exchange rate |
|---|---|
| € | 1.00000000000 |
| £ | 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:
- Is to add a column with the calculated revenue based on the exchange rate so I don’t have to use a formula.
- 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.