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 ne...
Need Help
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

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

    Hi Cypher294,

     

     To create another measure to work on State.

     

    STATE = CALCULATE(SUM(Sample_Data[Time in Hours]))/CALCULATE(DISTINCTCOUNT('Sample_Data'[Incident ID]),ALLEXCEPT(Sample_Data,Sample_Data[Ticket State]))

     

    Regards,

    Frank

    • Cypher294's avatar
      Cypher294
      Frequent Visitor
      7 years ago

      Hi v-frfei-msft,

       

      sorry it doesn't work until now ... For example the total hours for October is 1198,96. Divided by 17 Uniq Incidents for Oktober the Average should be ~ 70. These 70hours should be seperated by team and state within the two graphs but in both cases the Sum is 70. If you would select for example team 1 ( which has less than 70 for example the sum for Team 1 should also be seperated by the states but the sum should be the same ). Hopefully you understand what I mean.

       

      currently in the last sample data the average in both graphs is not ~ 70 and additional different on both perspectives (SUM Group 135 and state 110 hours )

       

      Maybe there is a way to fix it.

       

      Thanks

       

      Dennis