Weekly group average

[sdgiss]

Hello all,

I am hoping someone can help me with calculating a weekly average for a group. The following returns the sum of the weekly totals for all of the racers who were competing in that respective week (which is the result I get when dropping [Total Dist] into the pivot table by itself). The desired value is next to Fall-Week01 for both 2019 and 2020. I believe I need a count of athletes per week to get to the outcome I desire, but that is proving difficult as well. The second measure is close but I get the same racer count (total of all racers in database) for each week even though the total number of racers per week never tops 24 in 2020.

Thank you, in advance, for the help!

Weekly average dist:=AVERAGEX(VALUES(dDate[WeekNum]),[Total Dist])

Weekly Average Dist-distinctcount:=DIVIDE(AVERAGEX(VALUES(dDate[WeekNum]), [Total Dist]),DISTINCTCOUNT(dRacer[Racer]))

[AlB · Accepted solution]

Hi @sdgiss 

To get an accurate answer, you'd need to show your data model, the tables relevant to these calculations and their relationships. If dRace is a dimension table, you have to do the distinctcount on the fact table where the info on the races is, since that will (supposedly) be filtered by the week number. Assuming [Total Dist] works correctly, try something like:

Measure =

DIVIDE( [Total Dist], DISTINCTCOUNT(FactTable[RacerID])

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[sdgiss]

Thank you @AlB! Your answer makes complete sense and have should have seen this when posting my question. It is such a simple solution, yet my narrow view of how this should work was blocking my ability to think clearly. I've gotten so caught up in always using my dimension tables that I forget about how, in some instances, they don't apply.

Have a great week!