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

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
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors