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? thanks

 

 

data model is as below

data model

 

Best regards,

ER

 

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

8 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi erihsehc

     

    What I would suggest doing is to use the new Divide Function which caters for Divide by Errors.


    So your measure could be written as:

    My Measure = DIVIDE(SUM(Sheet1[sales]),SUM(Sheet1[cost]))

    This will result in the Divide by errors being blank

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