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,

 

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

    • Cypher294's avatar
      Cypher294
      Frequent Visitor
      7 years ago

      Hi v-frfei-msft,

       

      I Thought I could adapt the sample data to my real database table environment with all relations etc. but if I use the statement within this environment the calculation seems to be faulty.

       

      I've attached a new pbix and seperated and relate the tables like they are in real.

       

      The calculation with the seperation by team seems to be correct on a monthly basis but if I use the same measure to separate by state it seems not. The expextation is that the 77+58 ( sum by team = 135  ) will be distributed by the states but currently the sum is bigger than 135.

       

      Maybe you could help again.

       

      Thanks for your great support it helps me a lot.

       

      Sample 3