Power BI - How to Average a Sum

[thomaslovell]

Please help.

I am working in Power BI.

My primary data table has a variable called "Distance" which is recorded within three levels: "Session", "Athlete" and "Drill".

In other words, within each Training Session, multiple Athletes participate in multiple drills, all of which have an associated distance. For example:

Session	Athlete	Drill	Distance
Monday	Bob Jones	Warm Up	1000
Monday	Bob Jones	Conditioning	5000
Monday	James Peters	Warm Up	800
Monday	James Peters	Conditioning	4800

<tbody>
</tbody>

I would like to visualise this data with the Total Distance per Athlete per Session, and finally, Average Total Distance for the session.

Session	Athlete	Total Distance
Monday	Bob Jones	6000
Monday	James Peters	5600

<tbody>
</tbody>

When visualising this data, I can only choose either Sum or Average. If I sum the Distance, it will Sum all athletes for the session. Alternatively, if I average the distance, it will Average the distance for each drill within the session.

Is it possible to FIRST sum the distance for each player within the session, and THEN average this summed distance?

Thankyou

 

[gazpage]

You will want something like:

=AVERAGEX (
VALUES ( Table[Athlete] ),
CALCULATE ( SUM ( Table[Distance] ) )
)