cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DRossi Helper I
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
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)




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


DRossi Helper I
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
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...





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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
Community Support

Re: Result is (blank) for a specific number

Hi @DRossi 

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

Capture3.JPG

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.

DRossi Helper I
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))

 

datatable.PNG

Highlighted
Community Support
Community Support

Re: Result is (blank) for a specific number

Hi @DRossi 

Still can't reproduce this problem.

Capture2.JPG

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors