Forum Discussion

Pretengineer's avatar
Pretengineer
Frequent Visitor
4 years ago
Solved

Measure Not Correct Currency

Have a table called Cost_Data and it has costs in three different currencies USD, GBP, and EUR.  Another table called Currency_Convert with Columns Currency, Symbol, Format.  I am trying to have a sl...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Pretengineer ,

    The reason why the line chart is not displayed after using the new measure is that the data obtained in the FORMAT format is essentially a text string. Even if the data format is changed to number or currency, it cannot be sorted by size, but only in alphabetical order. 

    But the Values in the line chart should be of number data type, so it can't display anything.

    If you want the symbol displaied correctly in the line chart, you should have three measures, set the corresponding currency symbol for each measure, here's my solution.

    Cost EUR' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "EUR",
    MAX ( 'Cost_Data'[Cost EUR] ),
    BLANK ()
)

    Cost GBP' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "GBP",
    MAX ( 'Cost_Data'[Cost GBP] ),
    BLANK ()
)

    Cost USD' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "USD",
    MAX ( 'Cost_Data'[Cost USD] ),
    BLANK ()
)

    It get the correct result for each currency.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

Hi Pretengineer ,

 

This seems to be a somewhat known issue. Since the format function translate the number to text, it works well in tables but not in graphs... I googled a bit and found this:

Solved: DAX to dynamically change the currency symbol faci... - Microsoft Power BI Community

 

So, my suggestion is to have two measures, one for the tables with the correct currency sign. And another one for graphs. that one still shows the correct exchange rate conversion, but it misses out on the currency symbol....

 

 

Here the measures (I fixed the total values also 🙂)

True Currency Number = 
Var USD_Cost = SUM(Cost_Data[Cost USD])
Var GBP_Cost = SUM(Cost_Data[Cost GBP])
Var EUR_Cost = SUM(Cost_Data[Cost EUR])
Var Currency_ID = Cost_Data[Currency Selected]

RETURN
SWITCH( 
    TRUE(),
        Currency_ID = "USD" , USD_Cost,
        Currency_ID = "GBP" , GBP_Cost,
        Currency_ID = "EUR" , EUR_Cost,
        0
)
True Currency Text = 
Var Currency_ID = Cost_Data[Currency Selected]
RETURN
SWITCH( 
   TRUE(),
        Currency_ID = "USD" , FORMAT(Cost_Data[True Currency Number],"$#.00"),
        Currency_ID = "GBP" , FORMAT(Cost_Data[True Currency Number],"£#.00"),
        Currency_ID = "EUR" , FORMAT(Cost_Data[True Currency Number],"€#.00"),
        0
)


I additionally changed the type of the measures to Currency for [True Currency Text] and to Decimal for [True Currency Number].

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/