simple way to calculate multiple metrics with similiar calculate logic

[oakfootballclub]

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

[DataNinja777 · Accepted solution]

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]

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.