Forum Discussion

erihsehc's avatar
erihsehc
Helper III
8 years ago
Solved

number formating issue

hi there,   I would like the number format in %, however, when I used formula ratio% = IFERROR(SUM(Sheet1[sales])/SUM(Sheet1[cost]),"na"), the result cannot be formatted in %, how to solve this? th...
Need Help
  • GilbertQ's avatar
    GilbertQ
    8 years ago

    Hi erihsehc

     

    This could possibly done by using the Format as shown below.

    My Measure =
IF (
    [Net Win] > 100,
    "nm",
    FORMAT ( DIVIDE ( SUM('Table1'[sales]), SUM('Table1'[Other Sales]), "NA" ), "Percent" )
)
erihsehc's avatar
erihsehc
Helper III
8 years ago

thanks GilbertQ, got it. However, we would like to show "na" (also exclude some extreme number by using if(>100,"nm") ) instead of blank for presentation. is that any method to show text but also format the number as %?

GilbertQ's avatar
GilbertQ
Super User
8 years ago

Hi erihsehc

 

This could possibly done by using the Format as shown below.

My Measure =
IF (
    [Net Win] > 100,
    "nm",
    FORMAT ( DIVIDE ( SUM('Table1'[sales]), SUM('Table1'[Other Sales]), "NA" ), "Percent" )
)
  • erihsehc's avatar
    erihsehc
    Helper III
    8 years ago

    thanks GilbertQ, this formula is working but there is another issue that, when I put this measure to a multi-row card, I can not format this %result, ("-1.28%" on the screenshot used this measure), I can not add label for it, and this % always stay on top, can not format the color and size.

    • GilbertQ's avatar
      GilbertQ
      Super User
      8 years ago

      Hi erihsehc

       

      Unfortunately there currently is no other way to format the measures so that they can change based on different scenario's.

       

      I would suggest going to https://ideas.powerbi.com and voting for the idea.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Could you consider a 2nd Formatting Measure for your cards?  Something like

        Formatted = Var MeasureResult = [YourMeasure]
RETURN
IF(
     MeasureResult = "na",
     "NA",
     (MeasureResult * 100) & "%"
)
    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

      Hi erihsehc,

      As GilbertQ posted, there is no way to format the measure. Please mark the solution GilbertQ shared as answer because it resolve your issue. So that more people will get useful/helpful reply as answer.

      Best Regards,
      Angelia

      • erihsehc's avatar
        erihsehc
        Helper III
        8 years ago

        ok thanks for the help:manhappy: