Forum Discussion

Jackoline_2023's avatar
Jackoline_2023
Regular Visitor
2 years ago

Currency Conversion Issues

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.
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Jackoline_2023's avatar
    Jackoline_2023
    Regular Visitor
    2 years ago

    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.