Power BI KPI Matrix Displaying Zero Target Value

[KarenSaunders]

Hi

I am new to posting, but hope someone will pickup and provide some guidance.

I am using the PowerKPIMatrix 3.1.1.  It works as expected except when I have a target value of ZERO.  The control displays a blank field.  This is a valid target - consider outstanding issues.

I have looked at different solutions, but none seem to resolve the issue.  

Please help!!!

Thanks

Karen

[Nouches]

I'm facing the same problem!

Has anyone found a workaround this? 

Thank you 

[v-jingzhang]

Hi @KarenSaunders

It seems KPI Status is calculated with a formula like DIVIDE([Current Value] - [Target Value], [Target Value]). So when a target value is zero, it returns the optional value blank as zero cannot be the divisor. 

Maybe you can create a measure to calculate the result by your own and put this measure into a Matrix visual to display the difference percentages. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Pragati11]

Hi @KarenSaunders ,

Can you try adding some more information like screenshots of the visual, etc. to explain your issue a bit more?

You can refer following blog on how to get your question answered on the forum:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...

Thanks,

Pragati

[KarenSaunders]

I have a table storing the target values.  Ones with >0 display as expected, but if the target =0 then the control displays blank.

Please let me know if you need more information.

[KarenSaunders]

Hi Jing

"

It seems KPI Status is calculated with a formula like DIVIDE([Current Value] - [Target Value], [Target Value]). So when a target value is zero, it returns the optional value blank as zero cannot be the divisor. 

Maybe you can create a measure to calculate the result by your own and put this measure into a Matrix visual to display the difference percentages. "

The logic makes sense, but could you please suggest how I use a measure without running into the same logic?

Thanks

Karen

[v-jingzhang]

Hi @KarenSaunders 

When the divisor is zero, any increase or decrease percentage would be infinite, which is mathematically meaningless. I'm not in your situation so I'm not able to suggest which optional result can meet your need better when the target value is zero. It's up to you. 

You can create a measure like below and replace "whatever" with the value you want for the special case when target value is zero. 

rate =
DIVIDE (
    SUM ( 'tablename'[Current Value] ) - SUM ( 'tablename'[Target Value] ),
    SUM ( 'tablename'[Target Value] ),
    "whatever"
)

I put it in a table visual as below. 

There are many discussions about how to calculate growth percent when start value is 0. You can refer to below discussions or google a bit to get some ideas. 

calculation - What is the correct answer for percent change when the start amount is zero dollars $0...

math - How to calculate percentage when old value is ZERO - Stack Overflow

How to get a percentage increase from a base of 0 in Excel - Quora

Best regards,

Jing

If this post helps, please Accept it as Solution to help other members find it.

[KarenSaunders]

Thanks for the replies, but maybe its not clear what I am trying.

The target value is an absolute Zero.   As in Zero High Impact incidents.

I am still not seeing a solution that allows the control to display and use the zero value as a target.

[v-jingzhang]

Hi @KarenSaunders 

I have a new idea today. Since this metric is for "Cyber Security Incidents" and its target value is zero, I think using the absolute difference between actual value and target value would be more appropriate than using a rate percentage as a KPI. Based on previous discussion, we know that the rate percentage is always infinite when target is zero and couldn't show the difference of impact degree. But the absolute number difference can show the impact degree.

If you can accept this idea, we can create a new measure like below to have a different KPI calculation method for "Cyber Security Incidents" metric specially. For other metrics, it still return the difference percentage. 

KPI =
IF (
    SELECTEDVALUE ( 'tablename'[Metric] ) = "Cyber Security Incidents",
    SUM ( 'tablename'[Current Value] ) - SUM ( 'tablename'[Target Value] ),
    DIVIDE (
        SUM ( 'tablename'[Current Value] ) - SUM ( 'tablename'[Target Value] ),
        SUM ( 'tablename'[Target Value] )
    )
)

Then put this measure and Metric, Current Value, Target Value columns into a table visual. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KarenSaunders]

Thank you for the suggestion, but the issue is not calculating/displaying the KPI value.

The issue is displaying the zero Target value in the control under the Threshold column.

I am still unable to fix this issue and it makes the control unusable.

Any help would be appreciated.

Karen

[Anonymous]

Hello,

I have exact same issue. If the target value is zero, then it still shows as blank (nothing is displayed in that cell!) and the KPI Status shows no value!

Is this issue/bug fixed or has anyone been able to find a way to make this work within the Microsoft KPI Matrix visualization and display the value 0 instead of "blank"?

Thanks in advance. Any help is much appreciated!