KPI (blank) when a single value is blank

[Knechtion]

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.

[v-robertq-msft]

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

2. Then I related these two tables:

3. I created a measure like this:

Measure =

IF(SUM('Table'[Value])=BLANK(),0,SUM('Table'[Value]))

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

[Knechtion]

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.