Forum Discussion

Knechtion's avatar
Knechtion
New Member
5 years ago

KPI (blank) when a single value is blank

I am using a KPI visual for multiple individual monthly metrics over the course of a year. There are 6 metrics but not all of them can occur every month. I have logic to handle if they select a date range in which there is NO data for that KPI.

 

I'm having an issue in which if ANY month within the range has a blank, then a KPI visual will display "(blank)".  I've read and tried a few methods of replacing the blanks with another value, however putting in this artificial value will change the final % being display in the KPI as it adds that value into the calculation.

 

Within a table view the data appears as expected and the "Total" properly calculates the % for that period of time. In the table below you can see that two of the metrics listed in the image don't have values for each month (yes I know they are out of order, it is on the to-do list to fix). The table & KPI are using a Measure which is a sum(passfail)/count(metric) (the passfail is a 0/1 value). The individual visuals are then doing the grouping beyond that.

 

 

As mentioned I tried replacing the blanks with 1's or 0's but then the final KPI value would use those in the calculation and show inaccurate total %. 

I appreciate any thoughts.

Need Help

2 Replies

  • Knechtion's avatar
    Knechtion
    New Member
    5 years ago

    Update: If I trend the KPI by Year instead of Month it resolved the "(Blank)" issue, though I'm still reviewing if it impacts the actual summary of the data.

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Knechtion 

    According to your description and sample pictures, I can clearly understand your problem, I have a little trick that can help to solve this problem in the Power BI, you can take a look at my steps and find if it’s helpful:

    This is my test data:

     

    1. I created a new table like this:
    ID = SUMMARIZE('Table',[ID])
    1. Then I related these two tables:

     

    1. I created a measure like this:
    Measure =

IF(SUM('Table'[Value])=BLANK(),0,SUM('Table'[Value]))
    1. I went to the original matrix and reaplce the fields in the matrix like this:

     

    And you can get what you want.

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.