Change DAX measure format based on Market selection

[hmokkapati]

Hello,

I have the below sample data model with different countries and Prices in those countries in the same table. 

We are not planning to do the Currency Conversion, but display the amounts in its local currency once the user selects the Market.

I wrote the below measure which works fine for Card kind of displays but not Column/Line Charts as it is a Text and not a number/currency format.

Total With Conversion = if (
FIRSTNONBLANK(CurrencyConversionTest[Market],1) = "France" || FIRSTNONBLANK(CurrencyConversionTest[Market],1) = "Germany", Format([TotalCurrency],"€#,##0") ,
if (
FIRSTNONBLANK(CurrencyConversionTest[Market],1)= "Australia", Format([TotalCurrency],"$#,##0"),
if (FIRSTNONBLANK(CurrencyConversionTest[Market],1)= "UK", Format([TotalCurrency],"£#,##0"))
)
)

How can I change the format to be a number so i can use this in Column/Bar charts too? Or is there some other approach that I need to follow and change my model? 

Please advise.

Thank You,

Harisha

[parry2k]

Goto modelling tab and change it to number format (Whole number etc)

[hmokkapati]

Hello parry2K

It wouldn't let me because of the way the Measure is written it treats it as "Text. The other options are disabled to choose.

The requirement is to be able to change the format of the measure to different currencies based on the country selected in the slicers.

Thank You,

Harisha

[parry2k]

Quick question, seems odd but do you want chart to show mixed currencies?

[Sean]

@parry2kThe way I read the question is that @hmokkapati wants

a SINGLE COLUMN that will display the VALUES as data type NUMBER but formatted as different currencies on EACH ROW

EDIT: So he has 1 Column with numbers and he wants the values on the rows within that 1 column to be formatted differently?

EDIT2: @hmokkapati

Regarding the chart what you can do is plot a total measure without the conversion and then don't display the labels

And then place your measure with the conversion in the tooltips

Granted that's not what you want - but you'll be plotting numbers and the tooltips will show those numbers as formatted text!

[parry2k]

Yeah @Sean I noticed that. As you suggested, Tooltip seems to be the best solution.

[hmokkapati]

Hello,

Thanks for your replies.

For now, planning to display a label below the charts which mentions the currency format (for ex: Sales in USD ($) or Sales in Euros (€) ) when the user selects the Market. The label content is built using a Measure.

Still would like to know if there is a workaround.

Thank You,

Harisha 

[parry2k · Accepted solution]

That is surely most elegant solution for now. Cheers!

[hmokkapati]

Sorry forgot to mention, User would select only one Market at a time.