Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculating average by group

Hi,

I'm doing some experimentation on calculating the average based on a group using the following sample data:

sample Data

I want to display the average number of points a given team has scored at home. For example, LAL played 4 games, with an average of 113.25 points at home.

 

I created the following measure to calculate the average:

AvgHomePoints = AVERAGE(scoreboard_all[Home Team Score])

 

This would either return the average of all the games or the games I select. I don't want the average to change depending on the games I select (e.g. even if I don't select a LAL game, I still want the average for LAL to display 113.25). Is there a way to display the average, grouped by the team?

 

The equivalent SQL would be something line:

SELECT AVG([Home Team Score]), [Home Team] FROM scoreboard_all

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I am not sure I got it completely. But please try for category Average

    https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/td-p/362637

     

    Or try Quick measure. Right-click on any field or table choose the new quick measure.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amit.

       

      I went to the link you provided. One of the replies in that thread provided the following measure:

      AverageMeasure = CALCULATE ( AVERAGE ( table[value] ), ALLEXCEPT ( Table, table[category] ) )

       

      I tried this and it appears to do what I want.

       

      Sincerely,

       

      Jason

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    I do not see a Game column in your source data.  Assuming there is an addition column called Game, try this measure

    AvgHomePointsAll = CALCULATE([AvgHomePoints],ALL(scoreboard_all[Game]))

    Hope this helps.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    AvgHomePoints = CALCULATE ( AVERAGE(scoreboard_all[Home Team Score] ), FILTER ( ALL( scoreboard_all), scoreboard_all[Home Team] = MAX ( scoreboard_all[Home Team] ) ) )

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

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