cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Knechtion
New Member

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.

 

Table view.PNG

 

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.

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1623653612468.png

 

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

v-robertq-msft_1-1623653612470.png

 

  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:

v-robertq-msft_2-1623653612476.png

 

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

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!