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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Session[/TD]
[TD]Athlete[/TD]
[TD]Drill[/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Bob Jones[/TD]
[TD]Warm Up[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Bob Jones[/TD]
[TD]Conditioning[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]James Peters[/TD]
[TD]Warm Up[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]James Peters[/TD]
[TD]Conditioning[/TD]
[TD]4800[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Session[/TD]
[TD]Athlete[/TD]
[TD]Total Distance[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Bob Jones[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]James Peters[/TD]
[TD]5600[/TD]
[/TR]
</tbody>[/TABLE]


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] ) )
)