number formating issue

[erihsehc]

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

[GilbertQ]

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]

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 · Accepted solution]

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]

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.

[v-huizhn-msft]

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]

ok thanks for the help

[GilbertQ]

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]

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

Formatted = Var MeasureResult = [YourMeasure]
RETURN
IF(
     MeasureResult = "na",
     "NA",
     (MeasureResult * 100) & "%"
)