4 Replies Latest reply on Jun 24, 2017 7:22 AM by Jim Dehner

    Calculate Daily KPIs

    Shiva Srinivasan

      All,

       

      I have Daily outage metrics is the following form

       

         

      CategoryDateEventIDTarget Value
      Mean Time to Restore2-Jun-171600
      Mean Time to Restore3-Jun-171600
      Mean Time to Restore4-Jun-171600
      Mean Time to Restore5-Jun-171600
      Mean Time to Restore6-Jun-171600
      Mean Time to Restore7-Jun-171600
      Mean Time to Restore8-Jun-171600
      Mean Time to Restore9-Jun-171600
      Mean Time to Restore10-Jun-171600
      Mean Time to Restore11-Jun-171600
      Mean Time to Restore12-Jun-171600
      Mean Time to Restore13-Jun-171600
      Mean Time to Restore14-Jun-171600
      Mean Time to Restore15-Jun-171600
      Mean Time to Restore16-Jun-171600
      Mean Time to Restore17-Jun-171600
      Mean Time to Restore18-Jun-1716025
      Mean Time to Restore18-Jun-1726067
      Mean Time to Restore18-Jun-17360123
      Mean Time to Restore19-Jun-171600
      Mean Time to Restore20-Jun-171600
      Mean Time to Restore21-Jun-171600

       

      Description of the data

      1. Everyday we track outages. There could be multiple outage in a single day.

      2. The outage goal is marked "Met" if the duration of the outage is less than the Target, else marked "Failed" (I was able to accomplish this easily....see attached workbook)

       

      Problem

      1. I need to calculate a value for every row Called "Daily KPI" which should be "Failed" if any one outage event in a day failed to been the Target.

          Example (For 18-Jun-17 in the above data)

           a. There were 3 outages

           b. 1 outage was 25 mins which was below the Target

           c. 2 outages were above Target

           d. So for 18-Jun-17 the Daily KPI should be "Failed" for all 3 rows

       

      Is this possible. If yes, please give me some ideas.

       

      Attached is the workbook. The first sheet displays the outages that occurred over the year and the colors indicate the unique outage in a particular day. The second sheet is tables which displays Met Goal? based on each outage. I need another field which displays Daily KPI which should be either "Met" or "Failed" depending on the number of outages that were above the Target for that day.