Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AnnaZ
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!

11 REPLIES 11
HenkVlootman
Regular Visitor

The only way to solve this issue for me is a (very) ugly method. I created two tables, each with the appropriate format and the use the selection and bookmarks panes and show / hide the graphs I want to see by using the slicer. One extra graph in my model, but it works.

lizgemma
Regular Visitor

I struggled with this too! I created a measure that choose between two other measures - but essentionally the measure used to pick between the percentage value and the whole number result needed the format to be changed. So if it is less than 1 then it is a percentage and above 1 it is a whole number. This might not be the case for your data but you can just adjust the formula to suit you. Hope this helps. 🙂 

lizgemma_0-1691416784277.png

*note I have selected Format of the measure not the Measure part. This is my Y-Axis value. 

 

Update: 

lizgemma_1-1691421516675.png

 

MaksymMyronenko
New Member

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/

thats great, however can u advise if there is a way to do it without tabular editor

himanshu56
Resolver II
Resolver II

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 

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

 

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.

@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"))

 

@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,

Anonymous
Not applicable

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.