Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper V
4 years ago
Solved

Fixed Exchange Rate

Hello Everyone,

 

I have a small problem...i can think of a solution but I am not sure if its the best one so I will need your advice about that.

 

I have a table with sales, cost and profit per country and city for 2021.

I also have another table with fixed exchange rate. This rate won't change throughout the year it will stay stable.
I would like to have both options for my reports. Local Currency and euro.

 

The sales table is:

YearCountryCitySales (Local Currency)Cost (Local Currency)Profit (Local Currency)
2021UKLondon                        100.000                          33.333                            66.667  
2021UKBristol                       150.000                          50.000                          100.000  
2021UKLiverpool                         50.000                          16.667                            33.333  
2021FranceParis                       300.000                        100.000                          200.000  
2021FranceMarseille                         60.000                          20.000                            40.000  
2021FranceLyon                         45.000                          15.000                            30.000  
2021USANY                   1.000.000                        333.333                          666.667  
2021USAArizona                        600.000                        200.000                          400.000  
2021IndiaNew Delhi                 10.000.000                    3.333.333                      6.666.667  
2021IndiaMumbai                   8.500.000                    2.833.333                      5.666.667  

 

The Exchange Rate table is:

Country    Exchange Rate
India85,00000
UK1,19000
France1,00000
USA1,11000

 

I was thinking to use a lookupvalue dax and add a calculated column in my sales table with the exchange rate column and create new columns for sales, cost and profit in euro. 

I was wondering if I can achieve the same results with measures and avoid creating so mane calculated columns.
Thank you very much in advance!!!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I tried to create it in the next page in the attached pbix file.

    please check the below picture and the attached pbix file.

     

     

    Sales currency slicer: =
SWITCH (
    SELECTEDVALUE ( 'Currency Select'[Currency select] ),
    "Local", IF ( HASONEVALUE ( Sales[Country] ), SUM ( Sales[Sales (Local Currency)] ) ),
    "Euro",
        SUMX (
            sales,
            CALCULATE (
                DIVIDE (
                    SUM ( Sales[Sales (Local Currency)] ),
                    LOOKUPVALUE (
                        ExchangeRate[Exchange Rate],
                        ExchangeRate[Country], SELECTEDVALUE ( Sales[Country] )
                    )
                )
            )
        )
)

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I only created the sales amount, and I hope you can apply the same logic to Cost and Profit as well.

    • Dimitris_Kats's avatar
      Dimitris_Kats
      Icon for Helper V rankHelper V
      4 years ago

      First of all thank you very much for your reply! I appreciate it!!

      To be honest i wanted to have only 2 selections on the currency slicer : Local and euro.
      If they select local currency they will see the sales exactly in the way I have them in the sales table. If they select euro they will have the sales in euro based on the exchange rates on the second table.
      Do you think this is possible?

      Thank you very much again for your time and help!

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        I tried to create it in the next page in the attached pbix file.

        please check the below picture and the attached pbix file.

         

         

        Sales currency slicer: =
SWITCH (
    SELECTEDVALUE ( 'Currency Select'[Currency select] ),
    "Local", IF ( HASONEVALUE ( Sales[Country] ), SUM ( Sales[Sales (Local Currency)] ) ),
    "Euro",
        SUMX (
            sales,
            CALCULATE (
                DIVIDE (
                    SUM ( Sales[Sales (Local Currency)] ),
                    LOOKUPVALUE (
                        ExchangeRate[Exchange Rate],
                        ExchangeRate[Country], SELECTEDVALUE ( Sales[Country] )
                    )
                )
            )
        )
)