topic Re: number formating issue in Service

number formating issue

erihsehc — Wed, 29 Nov 2017 02:43:23 GMT

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,

Re: number formating issue

GilbertQ — Wed, 29 Nov 2017 03:21:23 GMT

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

Re: number formating issue

erihsehc — Wed, 29 Nov 2017 04:05:46 GMT

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

Re: number formating issue

GilbertQ — Wed, 29 Nov 2017 06:29:34 GMT

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

Re: number formating issue

erihsehc — Wed, 29 Nov 2017 07:42:14 GMT

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.

Re: number formating issue

GilbertQ — Thu, 30 Nov 2017 04:04:26 GMT

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.

Re: number formating issue

Anonymous — Thu, 30 Nov 2017 05:52:30 GMT

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

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

Re: number formating issue

v-huizhn-msft — Thu, 30 Nov 2017 08:26:52 GMT

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

Re: number formating issue

erihsehc — Thu, 30 Nov 2017 09:31:17 GMT

ok thanks for the help:manhappy: