Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power BI KPI Matrix Displaying Zero Target Value

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 cont...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

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? - Personal Finance & Money Stack Exchange

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi Anonymous 

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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