Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate average on data that are summarized

I have a table with case event data


How do I calculate the average where the durations are first summarised per case, and then divided by total number of cases. When I try now I'm only getting an average per row. 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try measures like

     


    Averagex(values(Table[Casenumber]), calculate(sum(Table[Duration In Min])) )

     

    or

    Averagex(summarize(Table, Table[Casenumber],"_1", calculate(sum(Table[Duration In Min])) ),[_1])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please provide some sample data and desired output. How many cases are the above data for？

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is sample data from two cases:

       

      I want my average to be:
      (546+339+2134+22+5) + (16) / 2 = 3046 + 16 / 2 = 1531
      In other words:
      total duration of case 1 + total duration case 2 / number of cases. 

      This should just be a messure so that I can display the average case duration in for example a card

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Create a measure.

    Measure = var case_1 = SUMX(ALL('Table'),'Table'[Duration])
var number = CALCULATE(DISTINCTCOUNT('Table'[CaseNumber]),ALL('Table'))
var result = DIVIDE(case_1,number)
return result

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.