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

  • 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