Forum Discussion

FlyKick's avatar
FlyKick
Helper II
6 years ago

Calculating % Close Rate For Service Tickets Over Time Period

Hi,   First and foremost, thanks to all the experts that frequent this forum and give up their time to help out. As a Power BI newbie I can't tell you how many times I have found the answer to a pr...
Need Help
az38's avatar
az38
Community Champion
6 years ago

Hi FlyKick 

I didnt check but maybe it would be better to use next to avoid any invisibe filters

Total Closed Tickets = calculate(counta('TicketHistory'[Closed Date]), ALL('TicketHistory'))

 

  • FlyKick's avatar
    FlyKick
    Helper II
    6 years ago

    AZ38 thanks for the tip but I that didn't work 😞 The challenge here is 

     

    I want to calculate the Close Rate % =  Total Closed Tickets  / Total OpenedTickets 

     

    I want to use a time period of last week (ideally it would be great if I can adjust to any time period using a slicer) but not essential.

     

    A Closed ticket is any ticket with a Closed Date in the time period.

    An Opened ticket is any ticket with an Entered Date in the time period.

     

    I can use two slicers one for Closed Date and one for Open Date on the visual but as the logic is AND it will only show a ticket that was Opened AND Closed in the time period. Therefore the Close Rate % is always 100%. 

     

    I know I need to use a measure to achieve this but I don't know if it is possible to 

     

    a) use a time period within a measure? 

    b) if a is possible use a variable or provider some interactive way for the user to adjust the time period variable

     

    This measure accurately counts the total closed tickets

    Total Closed Tickets = counta('Current-Ticket History'[Closed On])

     

    This measure only counts all the tickets that are still open. I.e ones that have no close date, it does not count tickets that were entered and closed in the time period.

    Total Open Tickets = CALCULATE( COUNTROWS('Current-Ticket History'), 'Current-Ticket History'[Closed On] = BLANK())

     

    If I was to write it out as pseudo code it would look something like

    1. TimePeriod = User Defined Date Period (i.e. last week or last month or custom period)
    2. ClosedTicketsVar = Count of all tickets with a closed date in $TimePeriod
    3. OpenTicketsVar = Count of all tickets with an entered date in $TimePeriod
    4. Ticket Close Rate = $ClosedTicketsVar / $OpenTicketsVar