Forum Discussion
Dynamic currency conversion based on Account Manager location in Power BI
Hi Team,
I have a requirement in Power BI where the dashboard currency needs to dynamically change based on the Account Manager’s booking location.
Scenario:
- The report contains revenue data in multiple currencies (e.g., USD, AUD, etc.).
- There is also a currency slicer in the dashboard.
- Additionally, users can filter/select an Account Manager.
NOTE:Currently, in my Power BI model, I have a relationship between the Booking Country dimension and the Fact table using Booking Country Code.
Now, I have an additional requirement where the reporting currency should dynamically change based on the Account Manager’s booking location (e.g., if Account Manager is from Japan → show values in JPY).
To support this, I am planning to introduce relationships involving:
- Account Manager
- Booking Country
- Currency
Requirement:
- If the selected Account Manager’s booking location is Japan, all revenue values in the report should automatically be converted and displayed in JPY (Yen), regardless of the currency selected in the slicer.
- If the Account Manager is from any other country, the report should follow the selected currency from the slicer.
Ask: What is the best approach to implement this dynamic logic in Power BI using DAX?
Hi Harini07,
Hope you're doing well!
First, ensure your model has these relationships:
FactRevenue → BookingCountry (via BookingCountryCode)
AccountManager → BookingCountry (via CountryCode)
CurrencyRates [lookup table, unrelated - accessed via TREATAS or LOOKUPVALUE]
Keep your Currency slicer disconnected (no relationship) from the Fact table, you'll control it entirely via DAX.
Step 1: Detect the Selected Account Manager's Currency
SelectedAM_Currency =
VAR SelectedAM =
SELECTEDVALUE(AccountManager[AccountManagerName])
VAR AMCurrency =
CALCULATE(
SELECTEDVALUE(BookingCountry[CurrencyCode]),
FILTER(
AccountManager,
AccountManager[AccountManagerName] = SelectedAM
)
)
RETURN
AMCurrency
Step 2: Determine the Effective Currency (Core Logic)
EffectiveCurrency =
VAR SelectedAM =
SELECTEDVALUE(AccountManager[AccountManagerName])
VAR AMCurrency =
CALCULATE(
SELECTEDVALUE(BookingCountry[CurrencyCode]),
FILTER(
AccountManager,
AccountManager[AccountManagerName] = SelectedAM
)
)
VAR SlicerCurrency =
SELECTEDVALUE(CurrencySlicer[CurrencyCode], "USD") -- fallback default
VAR IsJapanAM =
AMCurrency = "JPY"
RETURN
IF(
ISBLANK(SelectedAM), -- No AM selected → use slicer
SlicerCurrency,
IF(
IsJapanAM, -- AM is from Japan → force JPY
"JPY",
SlicerCurrency -- Other AM → use slicer
)
)
Step 3: Converted Revenue Measure
Revenue_Converted =
VAR EffCurrency = [EffectiveCurrency]
VAR ExchangeRate =
CALCULATE(
SELECTEDVALUE(CurrencyRates[Rate]),
CurrencyRates[ToCurrency] = EffCurrency,
CurrencyRates[FromCurrency] = "USD" -- assuming USD as base currency
)
VAR BaseRevenue =
SUM(FactRevenue[RevenueUSD])
RETURN
IF(
ISBLANK(ExchangeRate),
BaseRevenue, -- Fallback: no conversion if rate missing
BaseRevenue * ExchangeRate
)
Step 4: Dynamic Currency Label (for visuals)
CurrencyLabel =
"Revenue (" & [EffectiveCurrency] & ")"
Use this as your visual title via the dynamic title option in the Format pane.
Hope this helps! Don't forget to mark as solution ✅ and thumbs up 👍 in order to keep helping others.
Best regards,
Oussama (Fabric & Power BI Consultant)
6 Replies
Since there are multiple currencies involve and the conversion will be to not just a specific currency, you will need to have a table that contains the conversion factor of each currency to another and depending on whether the conversion factor each is date dependent, you will need to have this information as well. If you could provide a sample data with all the relevant information, it would be easier to come up with a solution. Just ensure your sample data can be easily copy pasted to Excel.
Hi Harini07,
Hope you're doing well!
First, ensure your model has these relationships:
FactRevenue → BookingCountry (via BookingCountryCode)
AccountManager → BookingCountry (via CountryCode)
CurrencyRates [lookup table, unrelated - accessed via TREATAS or LOOKUPVALUE]
Keep your Currency slicer disconnected (no relationship) from the Fact table, you'll control it entirely via DAX.
Step 1: Detect the Selected Account Manager's Currency
SelectedAM_Currency =
VAR SelectedAM =
SELECTEDVALUE(AccountManager[AccountManagerName])
VAR AMCurrency =
CALCULATE(
SELECTEDVALUE(BookingCountry[CurrencyCode]),
FILTER(
AccountManager,
AccountManager[AccountManagerName] = SelectedAM
)
)
RETURN
AMCurrency
Step 2: Determine the Effective Currency (Core Logic)
EffectiveCurrency =
VAR SelectedAM =
SELECTEDVALUE(AccountManager[AccountManagerName])
VAR AMCurrency =
CALCULATE(
SELECTEDVALUE(BookingCountry[CurrencyCode]),
FILTER(
AccountManager,
AccountManager[AccountManagerName] = SelectedAM
)
)
VAR SlicerCurrency =
SELECTEDVALUE(CurrencySlicer[CurrencyCode], "USD") -- fallback default
VAR IsJapanAM =
AMCurrency = "JPY"
RETURN
IF(
ISBLANK(SelectedAM), -- No AM selected → use slicer
SlicerCurrency,
IF(
IsJapanAM, -- AM is from Japan → force JPY
"JPY",
SlicerCurrency -- Other AM → use slicer
)
)
Step 3: Converted Revenue Measure
Revenue_Converted =
VAR EffCurrency = [EffectiveCurrency]
VAR ExchangeRate =
CALCULATE(
SELECTEDVALUE(CurrencyRates[Rate]),
CurrencyRates[ToCurrency] = EffCurrency,
CurrencyRates[FromCurrency] = "USD" -- assuming USD as base currency
)
VAR BaseRevenue =
SUM(FactRevenue[RevenueUSD])
RETURN
IF(
ISBLANK(ExchangeRate),
BaseRevenue, -- Fallback: no conversion if rate missing
BaseRevenue * ExchangeRate
)
Step 4: Dynamic Currency Label (for visuals)
CurrencyLabel =
"Revenue (" & [EffectiveCurrency] & ")"
Use this as your visual title via the dynamic title option in the Format pane.
Hope this helps! Don't forget to mark as solution ✅ and thumbs up 👍 in order to keep helping others.
Best regards,
Oussama (Fabric & Power BI Consultant)
The fix is a small measure that reads AM’s country with SELECTEDVALUE() and returns "JPY" when it is JP and otherwise the slicer value, and you pass that into the revenue conversion.
Hi Harini07
This requirement can be solved cleanly using a dynamic currency conversion pattern in DAX combined with disconnected or conditional table logic.
To implement this, ensure you have a standard Exchange Rates table that contains the conversion factors from your base currency to all target reporting currencies. Next, make sure your Account Manager dimension is linked to their respective Booking Country so that selecting a manager exposes their default location.
The best approach is to handle this logic directly inside a core DAX measure that dynamically determines the target currency before applying the conversion rate. You can achieve this by using a measure structured like this:
Dynamic Revenue = VAR SelectedManagerCountry = SELECTEDVALUE('Account Manager'[Booking Country]) VAR SlicerCurrency = SELECTEDVALUE('Currency Slicer'[CurrencyCode]) VAR TargetCurrency = IF( SelectedManagerCountry = "Japan", "JPY", SlicerCurrency ) VAR ConversionRate = LOOKUPVALUE( 'Exchange Rates'[Rate], 'Exchange Rates'[CurrencyCode], TargetCurrency ) RETURN SUM('Fact Sales'[BaseRevenue]) * COALESCE(ConversionRate, 1)In this pattern, the TargetCurrency variable evaluates whether the filtered Account Manager's country is Japan. If it evaluates to true, it overrides the slicer selection and forces the target currency to "JPY"; otherwise, it defaults to whatever currency is active in the user's slicer selection. The measure then looks up the corresponding rate from your exchange rate table and multiplies it by your base revenue metric, ensuring the dashboard seamlessly adapts its figures based on who is selected.
Hi Harini07,
Have you had a chance to review the solution we shared by oussamahaimoud Kagiyama_yutaka Poojara_D12 danextian?If the issue persists, feel free to reply so we can help further.
Thank you.
HI Harini07,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
