calculating average by group

[Anonymous]

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

[v-xicai]

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.

[Ashish_Mathur]

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.

[amitchandak · Accepted solution]

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]

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