Currency Conversion Issues

[Jackoline_2023]

Good day

I have an issue with my currency conversion measure.

They work fine I can get the currency conversion no problem but I can't seem to format the amounts with the Select currency's symbol.

If the selected currency is ZAR, the the value should be R2500 but if Naira is selected, then the value should be ₦2500 (well the amount will be converted to the Naira exchange rate). When I but the symbol on the value, then the measure returns blank.

Claim Amount with Currency 2 =

  CALCULATE([Claim Amount 2] * [Exchange Rate])

Claim Amount 2 =

    CALCULATE(

        SUM(Commercial[sbgl_amount_base]),

        ALL(TransactionCurrency[currencyname]),

        Commercial[sbgl_commercialtype_display] in {

            "Claim against ",

            "Claim against stakeholder" ,

            "Claim Amount"

        }

    )

Exchange Rate =

VAR SelectedCurrency = SELECTEDVALUE(TransactionCurrency[currencyname], "South African Rand")  

VAR ExchangeRate =  

    IF(SelectedCurrency = "South African Rand", 1,  

        LOOKUPVALUE(TransactionCurrency[exchangerate],  

            TransactionCurrency[currencyname], SelectedCurrency))  

RETURN ExchangeRate  

I just need the currency symbol to be added to the format, everything else works fine.

Another issue:

When products are captured in a selected country, when I create a matrix visual, the matrix returns all the products captured instead of the products captured in that selected country.

[Jackoline_2023]

Hi @Anonymous 

I guess it could work but I wanted an option that doesn't include me having 19 different switch statements because I have to convert the money into 19 different currencies.

[Anonymous]

Hi @Jackoline_2023 ,

Try using FORMAT function to change currency format to text.

Measure = 
VAR SelectedCurrency = SELECTEDVALUE(TransactionCurrency[currencyname], "South African Rand")  
VAR ExchangeRate =  
    IF(SelectedCurrency = "South African Rand", 1,  
        LOOKUPVALUE(TransactionCurrency[exchangerate],  
            TransactionCurrency[currencyname], SelectedCurrency))  
VAR ClaimAmount2 =
    CALCULATE(
        SUM(Commercial[sbgl_amount_base]),
        ALL(TransactionCurrency[currencyname]),
        Commercial[sbgl_commercialtype_display] in {
            "Claim against ",
            "Claim against stakeholder" ,
            "Claim Amount"
        }
    )
VAR ClaimAmountWithCurrency2 = CALCULATE(ClaimAmount2 * ExchangeRate)
RETURN
    SWITCH (
        SelectedCurrency,
        "South African Rand", FORMAT ( ClaimAmountWithCurrency2, "Currency" ),
        "Naira", FORMAT ( ClaimAmountWithCurrency2, "\"₦\"#,###.00" ),
        FORMAT ( ClaimAmountWithCurrency2, "General Number" )
    )

 3 Ways to Change Currency Format in Power BI - Power Tech Tips

The second question needs to be analyzed in light of the model. This is generally a relationship/filtering problem.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum