Need to change $ symbol format based on slicer selection

[Anonymous]

Hi,

i have a table with single column having two values(a derived table)

Toggle

Sales

Units

This i am pulling as a slicer. Now based on the value of 'Toggle' i need to switch b/w two measures
1. Sales_Measure
2. Units_Measure

Now Sales_measure is in $ format. Units is in decimal.

Now when i toggle b/w these two measures using a new conditional measure like

IF Toggle="Sales" then sales_measure else units_measure 

now this new conditional measure shows $ format at all times (value format set as Auto). Units in $ format doe not make sense.

Any way we can change the $ symbol also based on slicer selection?

I don't want to use bookmarks as the changes are many.

Regards,

Mukul

[dlopesc04]

It should be noted that nowdays we have Dynamic formating, solving this issue and working for charts as well.

[MFelix]

Hi  @Anonymous ,

If you are using and if statment to change between to measure that are in different formats you need to use the format option give you the correct format in your case:

MEASURE FORMATTED = IF (SELECTEDVALUE(Table[Toggle])="Sales" ;FORMAT( [sales_measure], "###.###,00$" ;FORMAT([units_measure]; "###.###" ))

You can play around with the formats if they are not correct with your model.

[Anonymous]

thanks @MFelix ,

I came up with exact same approach and i would have accepted this as an answer BUT BUT....
I tested it quite a bit. FORMAT function converts measure into string. 

In case of card visual that works but when i try to put this new measure into some visual like bar chart, donut, etc.
it doesn't come in value section as it's in string format.

What should i do man!

[v-deddai1-msft]

Hi @Anonymous ,

Based on my test, it does not work even after you modify the data types of [sales_measure] and [units_measure].Using bookmarks may be the only way.

Best Regards,

Dedmon Dai

[Anonymous]

Yeah! that i checked too. Tried every possible way i guess till now.
We should have a funcitonality to change even formats also using conditional formatting or something. that would really help man!

[v-deddai1-msft]

 I suggest you create an idea in Power BI forum  https://ideas.powerbi.com/forums/265200-power-bi-ideas.

[Anonymous]

Done that.
anyone looking for this thing and wandering here please vote on below idea
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36957196-dynamic-currency-formatt...

[MFelix]

Hi @Anonymous ,

That's the problem with format.

If you are using charts best options is two charts and a bookmark

[amitchandak]

You can change the format of the column or measure. Click on that, it should be under column tool in March 2020 release

[Greg_Deckler]

Try using the CONVERT function to convert between whole number or decimal number and currency.