Forum Discussion

Cypher294's avatar
Cypher294
Frequent Visitor
7 years ago

Average processingtime per ticket state / Measure required

Hello everybody,

 

I've got an problem creating a report for showing the average processing time for several tickets based on a processing table which includes each state for each ticket and the needed time of processing in each state ( each incident mentioned several times // one time for each state // Ticket ID is unique ).

 

The sample data is linked to this post.

 

In this example there are 9 Unique Incidents within August 2018. I've already created a measure to calculate the average processing time for each team within this month based on the number of unique tickets within this month ( 9 unique tickets within 14 rows ). In this case 56,26 hours ( total hours 506 divided through 9 not 14 )

 

 

 

 

The issue I have is that now I want to calculate the average processing time by each state for each UNIQUE incident.

The expectation is that e.g. the 66 hours for state " waiting" will be divided by 9 unique incidents for August ( 7,33 hours ).

Of course the full dataset includes several months and because of this the reports needs a logic which divides by the number of unique incidents for each month. The result would be that the sum of 56 hours will be spread through the states with a total of 56 hours. ( Currently the sum is >150 because of the single states will be summarized ) .

 

The PBIX could be find here:

 

Sample Data

 

Thanks for your help.

Need Help

8 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Cypher294,

     

    Please use this measure.

     

    Measure = CALCULATE(SUM(Sample_Data[Time in Hours]))/CALCULATE(DISTINCTCOUNT(Sample_Data[Incident ID]),ALLSELECTED(Sample_Data))

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • Cypher294's avatar
      Cypher294
      Frequent Visitor
      7 years ago

      HI v-frfei-msft,

       

      Thanks for your fast support. I've tried it out but there is one more issue. The calculation just work for a single months if there is more data for additional months the average seems to be calculated for all IDs not for the ones within the dedicated month.

       

      I've attached an additional sample data with more data to show the issue. if you select an explicit month it counts correctly but not if you not select one.

       

      Sample Data 2

       

      Thanks Dennis

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Cypher294,

         

        To update the measure as below.

         

        Measure = CALCULATE(SUM(Sample_Data[Time in Hours]))/CALCULATE(DISTINCTCOUNT(Sample_Data[Incident ID]),ALLEXCEPT(Sample_Data,Sample_Data[Date].[MonthNo]))

         

        Regards,

        Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Cypher294,

       

      To use the measure.

       

      Measure = CALCULATE(SUM(Sample_Data[Time in Hours]))/CALCULATE(DISTINCTCOUNT('Incidents 2'[Incident ID]),ALLEXCEPT('Date','Date'[Date].[MonthNo]))

      Regards,

      Frank

      • Cypher294's avatar
        Cypher294
        Frequent Visitor
        7 years ago

        HI v-frfei-msft,

         

        it looks well so long... But I have one more issue which cause currently some problems.

         

        Normally not every ticket ID which is recorded in " Incidents 2 " is recorded in "Sample_Data" which cause some problems.

        It divides by all the unique IDs recorded to Incident 2 table not just by the ones registered in BOTH.

         

        If I adjust the measure ( page 2 --> Measure 2 ) It counts right for "Group" seperation but not for "State".

         

        I've uploaded an adjusted sample data. Hopefully this is the last problem to be solved ...

         

        Sample Data 4