Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
oakfootballclub
Helper IV
Helper IV

simple way to calculate multiple metrics with similiar calculate logic

for example, there are lots of metris, e.g.  margin, revenue, cost, net_revenue, forecast_revenue, budget.....,

all these metrics are required to get their amount in many different currency, there are exchange rates such as HKD, USD, SGD, EUR, BND, LBP, ILS, GIP..

for example, margin * HKD will get the margin_hkd

if I need to transform all the metris in all the currency values, is there an easy solution with hurt the performance?

I would be very grateful if you could provide some sample DAX

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @oakfootballclub ,

To dynamically convert metrics like Margin, Revenue, and Cost into various currencies while maintaining efficiency, the best approach is to use pre-defined measures and a disconnected currency table.

First, create a disconnected table with exchange rates relative to USD using the following DAX formula:

 

CurrencyRates = 
DATATABLE(
    "Currency", STRING,
    "RateToUSD", DECIMAL,
    {
        {"USD", 1},
        {"HKD", 7.8},
        {"SGD", 1.35},
        {"EUR", 0.92},
        {"BND", 1.36},
        {"LBP", 1500},
        {"ILS", 3.7},
        {"GIP", 0.82}
    }
)

This table allows users to select a currency via a slicer without needing a relationship to the data model.

Next, create a measure to dynamically retrieve the exchange rate for the selected currency:

SelectedCurrencyRate = 
VAR SelectedCurrency = SELECTEDVALUE(CurrencyRates[Currency])
RETURN
LOOKUPVALUE(CurrencyRates[RateToUSD], CurrencyRates[Currency], SelectedCurrency)

This measure fetches the exchange rate for the currency chosen in the slicer.

To handle currency conversion dynamically for all metrics, create a general measure using ISSELECTEDMEASURE. This measure references the base measures and applies the selected currency's exchange rate:

ConvertedMetricValue = 
VAR Rate = [SelectedCurrencyRate]
RETURN
    SWITCH(
        TRUE(),
        ISSELECTEDMEASURE([Margin]), [Margin] * Rate,
        ISSELECTEDMEASURE([Revenue]), [Revenue] * Rate,
        ISSELECTEDMEASURE([Cost]), [Cost] * Rate,
        ISSELECTEDMEASURE([Net Revenue]), [Net Revenue] * Rate,
        ISSELECTEDMEASURE([Forecast Revenue]), [Forecast Revenue] * Rate,
        ISSELECTEDMEASURE([Budget]), [Budget] * Rate,
        BLANK()
    )

This measure dynamically applies the selected currency's exchange rate to the selected metric.

Add the Currency column from the CurrencyRates table as a slicer to enable users to select their preferred currency. Use the ConvertedMetricValue measure in your visuals, and it will dynamically display the selected metric in the chosen currency.

This approach avoids redundancy, ensures clarity, and provides scalability. Adding new currencies or metrics requires minimal updates, and users have the flexibility to interact with both metric and currency selections. Let me know if you need further assistance!

 

Best regards,

 

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

Hi @oakfootballclub ,

To dynamically convert metrics like Margin, Revenue, and Cost into various currencies while maintaining efficiency, the best approach is to use pre-defined measures and a disconnected currency table.

First, create a disconnected table with exchange rates relative to USD using the following DAX formula:

 

CurrencyRates = 
DATATABLE(
    "Currency", STRING,
    "RateToUSD", DECIMAL,
    {
        {"USD", 1},
        {"HKD", 7.8},
        {"SGD", 1.35},
        {"EUR", 0.92},
        {"BND", 1.36},
        {"LBP", 1500},
        {"ILS", 3.7},
        {"GIP", 0.82}
    }
)

This table allows users to select a currency via a slicer without needing a relationship to the data model.

Next, create a measure to dynamically retrieve the exchange rate for the selected currency:

SelectedCurrencyRate = 
VAR SelectedCurrency = SELECTEDVALUE(CurrencyRates[Currency])
RETURN
LOOKUPVALUE(CurrencyRates[RateToUSD], CurrencyRates[Currency], SelectedCurrency)

This measure fetches the exchange rate for the currency chosen in the slicer.

To handle currency conversion dynamically for all metrics, create a general measure using ISSELECTEDMEASURE. This measure references the base measures and applies the selected currency's exchange rate:

ConvertedMetricValue = 
VAR Rate = [SelectedCurrencyRate]
RETURN
    SWITCH(
        TRUE(),
        ISSELECTEDMEASURE([Margin]), [Margin] * Rate,
        ISSELECTEDMEASURE([Revenue]), [Revenue] * Rate,
        ISSELECTEDMEASURE([Cost]), [Cost] * Rate,
        ISSELECTEDMEASURE([Net Revenue]), [Net Revenue] * Rate,
        ISSELECTEDMEASURE([Forecast Revenue]), [Forecast Revenue] * Rate,
        ISSELECTEDMEASURE([Budget]), [Budget] * Rate,
        BLANK()
    )

This measure dynamically applies the selected currency's exchange rate to the selected metric.

Add the Currency column from the CurrencyRates table as a slicer to enable users to select their preferred currency. Use the ConvertedMetricValue measure in your visuals, and it will dynamically display the selected metric in the chosen currency.

This approach avoids redundancy, ensures clarity, and provides scalability. Adding new currencies or metrics requires minimal updates, and users have the flexibility to interact with both metric and currency selections. Let me know if you need further assistance!

 

Best regards,

 

danextian
Super User
Super User

Hi @oakfootballclub 

Do you intend to report in one currency or in different currencies? If in different currencies and you do have the local currency amount and the currency indicator then you can just use slicers.
However if you don't have the local currencies already, the question is how do you convert an amount to the local currency? Is it simply sum(table[local amount]) * rate or do you evaluate the conversion on a per row basis in your data as there may be different rate for each date?

It will be easier for us to understand what you're trying to do with a workable sample data and your expected result from that.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors