Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate an Average between two dates with additional selection

Hi All,   I have a table with Categories and open dates and closed dates, i've created a measure to calculate the time difference between the open and closed using: Time to Resolution (Minutes) = ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    You can use this measure, I also create a sample for your reference,

    AverageByCategory = 
    var _sumCategory=SUMX(ALLEXCEPT('deskpro ticket','deskpro ticket'[Category]),[Time to Resolution (Minutes)])
    var _count=COUNT('deskpro ticket'[Category])
return _sumCategory/_count

    result

     

     

    Best Regards,

    Community Support Team _Tang

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.