Dynamic format strings for currency conversion in PowerBi with DAX
I’ve previously written an article on how to dynamically convert and change the currency of a measure using a slicer.
Now let’s see how we can use dynamic format strings for currency conversion in PowerBi with DAX.
Model Prerequisite
Add the FormatString column into your currency dimension
You can download the file with the sample format string from here.
Make sure to use a Unicode type column to store the different format strings otherwise the collation of your DB might not support all the characters.
Note that the format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic.
FORMAT( YourMeasure,
SELECTEDVALUE(
DimCurrency[FormatString],
[optional] SelectedFormatString()-- or defaultCurrency
)
Here the trick is to use the Format() function and the SelectedValue() function.
The SelectedValue() function returns the value of the column FormatString when the context has been filtered down to one distinct value only.
[Optional] If it has not been filtered down to only a single value it will return the alternate result which can be blank or in my case set to USD by default. You can also use the SelectedFormatString() function.
And here is our dynamic currency formatting slicer
Here is the full dax script:
The _USDFormat variable retrieves the default format String for USD.
When no currency is selected the USD conversion and format are applied by default.
For more explanation about the rest of the code, you can refer back to my previous post here.
Sales Amount Converted:=
var _USDMeasure =sum(FactInternetSales[SalesAmount])
var _USDFormat = LOOKUPVALUE(DimCurrency[FormatString], [CurrencyName], "US Dollar") --retrieve default curency format
return
if(ISBLANK(_USDMeasure),blank(),
if(HASONEFILTER(DimCurrency[CurrencyName]),
-- the conversion calculation starts here
-- the above part is just used to handle null values
-- and slicer with multiple values allowed
var _sumx =sumx(
CROSSJOIN(
values(DimCurrency[CurrencyKey]),
values(DimDate[DateKey])
),
CALCULATE(
var _USDMeasurex =sum(FactInternetSales[SalesAmount])
return
Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate]))
)
)
return
FORMAT( _sumx, SELECTEDVALUE(
DimCurrency[FormatString],
_USDFormat
)
)
,FORMAT( _USDMeasure,_USDFormat)
)
)
Note that as mentioned in my previous post the type of this calculated measure will be Text, hence, you won’t be able to perform other dependant calculations based on this measure.
Or, you’ll need to maintain extra measures in your model where you keep the reference measures with no dynamic formatting and apply the formatting on the dependant measures.
2 thoughts on “Dynamic format strings for currency conversion in PowerBi with DAX”
Hi
I can’t seem to get the sample format string column completely right when I load it in to Power BI. Most currency signs seem to work somewhat but I cant for example get the arabic signs to load right (unless I load them as Arabic . How did you make i work?
I have tried to load it as many diffrent types och unicode columns but there is always some signs that doesnt work.
Hi Anton,
Did you use the sample format string provided here:https://powerbiblogscdn.azureedge.net/wp-content/uploads/2019/05/CurrencyFormatStrings.txt
Can you please try to use the following format: (this is basically the content of my dim currency table used for this post db sample adventureworksDW2016 + manual update to add the column format)
You can copy/paste the data into PBI directly.
CurrencyKey CurrencyAlternateKey CurrencyName FormatString
3 ARS Argentine Peso \$ #,0.00;-\$ #,0.00;\$ #,0.00
6 AUD Australian Dollar \$#,0.00;-\$#,0.00;\$#,0.00
16 BRL Brazilian Real “R$”#,0.00;-“R$”#,0.00;”R$”#,0.00
19 CAD Canadian Dollar \$#,0.00;-\$#,0.00;\$#,0.00
36 EUR EURO #,0.00 “€”;-#,0.00 “€”;#,0.00 “€”
61 MXN Mexican Peso \$#,0.00;-\$#,0.00;\$#,0.00
85 SAR Saudi Riyal #,0.00 “ر.س.”;-#,0.00 “ر.س.”;#,0.00 “ر.س.”
98 GBP United Kingdom Pound “£”#,0.00;-“£”#,0.00;”£”#,0.00
100 USD US Dollar \$#,0.00;-\$#,0.00;\$#,0.00
101 KRW Won “£”#,0.00;-“£”#,0.00;”£”#,0.00
102 JPY Yen “¥”#,0.00;-“¥”#,0.00;”¥”#,0.00
103 CNY Yuan Renminbi “¥”#,0.00;”¥”-#,0.00;”¥”#,0.00
105 PLN Zloty #,0.00 “zł”;-#,0.00 “zł”;#,0.00 “zł”
Also, there’s a better of achieving this now by using the calculation group available in PBI and SSAS 2019 or Azure AS.