cancel
Showing results for
Did you mean:
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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Highlighted
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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! 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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors