Custom Filter/Slicer to update the currency conversion

[sagargaryali01]

Hi All,

I need one help in updating the currency from local to USD.

So basically I have revenue measure coming from fact table for two different countries Japan and South Korea.

And I also have currency conversion table in which I have the conversion rate for both the countries.

Now the catch is in report when I filter out data for individula countries like suppose i selected Japan the revenue should be in Local currency similarly for korea.

But when I select both the countries it show me USD number and Japan revenue number should be multiplied with Japan conversion rate and korea with Korea conversion rate then it should show sum of both.

I know it is little complicated I am adding data below of for revenue and currency conversion.

Please help with this issue I need to provide the soltuion to the client. 

Country Code	Country Name	Conversion Rate
353	Japan	0.00782
354	South Korea	0.000765

Fiscalweek	Country Code	Country Name	Revenue
202401	353	Japan	35467290
202402	353	Japan	674387438
202403	353	Japan	3535782
202404	353	Japan	843653542
202405	353	Japan	893874367
202406	353	Japan	4373890
202407	353	Japan	7834378
202408	353	Japan	783487436
202409	353	Japan	900897823
202401	354	South Korea	45879809
202402	354	South Korea	874833
202403	354	South Korea	902378347
202404	354	South Korea	76346734
202405	354	South Korea	90238943
202406	354	South Korea	923653
202407	354	South Korea	33892650
202408	354	South Korea	9867435
202409	354	South Korea	974325723

[Anonymous · Accepted solution]

Hi @sagargaryali01 ,

Try formula like below:

Revenue USD =
VAR SelectedCountries =
    VALUES ( 'Fact'[Country Code] )
RETURN
    SUMX (
        FILTER ( 'Fact', 'Fact'[Country Code] IN SelectedCountries ),
        'Fact'[Revenue] * RELATED ( 'Currency Conversion'[Conversion Rate] )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sagargaryali01]

Hi 

Can anyone help me on this please