Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ticket resolution KPI dashboard

Hey everyone,
I've got a table with support tickets status history in following column format
Ticket Number - "IM1234"
StatsStatusTime - Data/Time
EndStatusTime - Date/Time
Duration - Duration in days i.e. "0.5" would mean status time half a day 12h

Each incident has single row per status entry in that table with rules as follows:
There can be only one status 'StartNew' as this is the 1st status when ticket gets created, than any number of multiply 'Investigation', 'Awaiting customer', 'Resolved' statuses mixed up in any order and number as the ticket gets progressed and investigated and than single status at the end 'Closed' which duration is always zero as it's last status. Everything as a single row per each status.

I've created a matrix visualization showing me the total time per individual Incident with breakdown including statuses:
meaning

Number | StartNew | Investigation | Awaiting Customer | Resolved | Closed | Total
IM1234 | 0.3 | 2.2 | 1.3 | 2.0| 0.0 | 5.8

However I'm also interrested except current Total in  lets call it Total2 summing up only StateNew and Investigation meaning in above excample Total2 = 0.3 + 2.2 = 2.5. It would be perfect if that would be in the same column next to Total.

I'd like to also build sort of KPI on this meaning if Total2 is grated than say 3 days I'd flag it with color coding or something else meaning this is already outside of KPI.

Any help or link to tutorial how to do this is appreciated !

Thanks
Evo

Need Help
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can set conditonal format based on total2 in 'Conditional Formatting' tab or click the down arrow of total2 in the values field to use conditional format as your needed:

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , did not get completed.

     

    For conditional formatting, you can create a measure like and used that with field option

     

    https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

    example

    color =
    switch ( true(),
    FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
    FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
    /// Add more conditions
    "red"
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry if I was not clear enough.

      I'd like to achieve this:

       

      Number | StartNew | Investigation | Awaiting Customer | Resolved | Closed | Total| Total2
      IM1234 | 0.3 | 2.2 | 1.3 | 2.0| 0.0 | 5.8|2.5

       

      Where Total2 is a sum of StartNew and Investigation and only base on Total2 I'd apply conditional formatting.

       

      Thanks

      Evo

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

        You can set conditonal format based on total2 in 'Conditional Formatting' tab or click the down arrow of total2 in the values field to use conditional format as your needed:

         

        Best Regards,
        Yingjie Li

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.