cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Dynamically format a measure as $ or %

Hi there,

 

Is there a way we can dynamically format how a measure X is displayed, either in $0.00 or %, depending on the values in another field? I have a separate field called 'Data Type' which I have indicated 'P' for percentage, 'A' for amount etc and I wish for this to control how measure X appears in $0.00 or %.

 

Thanks for your help. 

 

Cheers!

8 REPLIES 8
Highlighted
Resolver II
Resolver II

Re: Dynamically format a measure as $ or %

Hello @AnnaZ

 

Yes you can do that. 

Once a measure is created, go to the modeling tab.

Under modeling tab, look for a field "Format". Click on Format and select the type of value you want to use

 

sol.PNG

 

Thanks,

Himanshu 

Highlighted
New Member

Re: Dynamically format a measure as $ or %

Hi Himanshu,

 

This option will only allow me to choose one or the other. What I was after was to view the measure in both $ and % depending on another field value. Say for example, this other field value was 'P', then this measure X would be displayed in %. And if the other field value was 'A'. then the same measure X would be displayed in $.

 

Cheers

 

Highlighted

Re: Dynamically format a measure as $ or %

Try creating the measure with either an IF or SWITCH statement based on the value of the other field. You could then use the FORMAT() function to dynamically change the display format.

 

Something like = SWITCH('table'[other field],"Value A",FORMAT([measure],"Currency"),"Value B",FORMAT([measure],"Percent"))


Here's more detail on the different options you can supply for the FORMAT() function: https://msdn.microsoft.com/en-us/library/ee634924.aspx. Scroll down and see the options for Number and use either the pre-defined or custom numeric formatting options.

Highlighted
New Member

Re: Dynamically format a measure as $ or %

@deldersveld

 

I used the switch function as below but the new measure is now formatted as a text and cannot be added to the visual.

 

CurrentValueFormatted = switch([DataTypeMeasure],"P",format([CurrentPercentValue],"Percentage"),"A",format(HotelKPICalculation[CurrentPercentValue],"Currency"))

 

Microsoft
Microsoft

Re: Dynamically format a measure as $ or %

@AnnaZ

 

It's true. Once you use Format() function, the data type will be converted to text, it will throw error if you change it to numeric type.

 

It's not possible to give a dynamic format on a measure but still keep numeric data type.

 

Regards,

Highlighted
Advocate III
Advocate III

Re: Dynamically format a measure as $ or %

Hi! I'd very much like this to be suggested as a fix. We often want to be able to show dynamically formatted measures in graphs.

Highlighted
Advocate II
Advocate II

Re: Dynamically format a measure as $ or %

I've created an idea requesting that this be implemented. Please help vote it up! https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35921176-option-to-format-values-...

Highlighted
New Member

Re: Dynamically format a measure as $ or %

If anybody still wants to see a sollution, here is a possible one:
https://rollingaverage.com/set-up-measure-selection-with-dynamic-formatting-in-powerbi/

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors