Forum Discussion

AnnaZ's avatar
AnnaZ
New Member
9 years ago

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...
AnnaZ's avatar
AnnaZ
New Member
9 years ago

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

 

deldersveld's avatar
deldersveld
Resident Rockstar
9 years ago

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.

  • AnnaZ's avatar
    AnnaZ
    New Member
    9 years ago

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

     

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.