cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Result is (blank) for a specific number

Hi all

I have a formula that generates a percentage measure.

Customer Promise Rate = VAR COUNTOFJOBS =
DISTINCTCOUNT('Query1'[FA_ID])

VAR COUNTOFBROKENPROMISES =
CALCULATE(DISTINCTCOUNT('Query1'[FA_ID]),
'Query1'[Customer Promise] IN {"1"})

RETURN

IF(NOT ISBLANK (COUNTOFJOBS),
1 -
DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

I then have a nested IF formula that tell me if the result is within a range it gives an output such as 'Outstanding'

CP Ratings =
IF(
ISBLANK([Customer Promise Rate]), "Not Eligible",
IF(
[Customer Promise Rate] <= 1 && [Customer Promise Rate] >= 0.95,
"Outstanding",
IF(
[Customer Promise Rate] <= 0.94
&& [Customer Promise Rate] >= 0.90,
"Exceeds Expectations",
IF(
[Customer Promise Rate] <= 0.89
&& [Customer Promise Rate] >=0.85,
"Acheiving Expectations",
IF(
[Customer Promise Rate] <= 0.84
&& [Customer Promise Rate] >= 0,
"Below Expectations"
)
)
)
))

For some reason, that I can't figure out, if it results at 80% then the card shows (blank) instead of 'Below Expectations'. It works some times but then not others dependent on the slicers selected

6 REPLIES 6
Super User IV

## Re: Result is (blank) for a specific number

You have used

IF(NOT ISBLANK (COUNTOFJOBS),
1 -
DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

Can you change it to

IF(NOT (ISBLANK (COUNTOFJOBS)),
1 -
DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS),0)

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper I

## Re: Result is (blank) for a specific number

Hi @amitchandak

Tried it but it doesn't work, it stays as (blank) and it also does something weird to my graph, instead of what is seleceted on the date slicer, it pulls every date.

Super User IV

## Re: Result is (blank) for a specific number

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Community Support

## Re: Result is (blank) for a specific number

Hi @DRossi

As tested with your measures, it works on my side.

Could you show me an example or some sample data?

Is there any other tables used and what's relationship between them/

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

## Re: Result is (blank) for a specific number

Thanks @v-juanli-msft

I have one relationship so not sure how it's impacting it.

So using the formula below. FA_ID is just long numbers such as 312967463. Customer Promise is an IF calculated column that determines whether the time in one column is later than another column resulting in 1 for true 0 for false.

Customer Promise Rate = VAR COUNTOFJOBS =
DISTINCTCOUNT('Query1'[FA_ID])

VAR COUNTOFBROKENPROMISES =
CALCULATE(DISTINCTCOUNT('Query1'[FA_ID]),
'Query1'[Customer Promise] IN {"1"})

RETURN

IF(NOT ISBLANK (COUNTOFJOBS),
1 -
DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

Community Support

## Re: Result is (blank) for a specific number

Hi @DRossi

Still can't reproduce this problem.

You could check the measures with formula partly, for example

measure1=IF(
[Customer Promise Rate] <= 0.84
&& [Customer Promise Rate] >= 0,
"Below Expectations"
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors