Getting the average for summed weekly data

[Anonymous]

Hi all,

I currently work with 25 professional athletes, and I am trying to use Power BI daily to collect and visualise their GPS running data.

I have used a date table to create a measure that collects and organises the data into 'season weeks'. For example in week 20, Player A covered 30km while Player B covered 25km and Player C covered 27km.

When I try to visualise the the average distance covered by the entire team for a given season week, Power BI is taking an average of the daily amount each player has covered (each row in my dataset), rather than an average of the distance covered by each player in the given season week. For example, the average looks more like 5km rather than 28km.

If any one has any idea how to visualise an average value for a grouped data set that would be extremely helpful!

Kind regards

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Also, share a Calendar Table with a column of season weeks.

[Anonymous]

Hi, thank you for your reply:

Date table:

Date Table =

Var MinYear = YEAR ( MIN ( 'Load Database'[Date] ) )

Var MaxYear = YEAR ( MAX ( 'Load Database'[Date] ) )

RETURN

ADDCOLUMNS(

FILTER(

    CALENDARAUTO( ) ,

    YEAR( [Date] ) >=MinYear &&

    YEAR( [Date] ) <=MaxYear

    ),

    "Weekday Number", WEEKDAY ([Date], 2),

    "Day of Week", FORMAT([Date],"Dddd"),

    "Week Commencing", [Date]- WEEKDAY([Date],2)+1,

    "Year", YEAR ( [Date] ),

    "Month Number", MONTH ([Date]),

    "Month", FORMAT([Date],"mmmm"),

    "Season", SWITCH (TRUE(),

    [Date] >= DATE(2022,06,20)  && [Date] < DATE(2023,06,31),"2022-2023",

    BLANK())

)

Season Week column:

Season Week =

--Inputs--

VAR WeekStartsOn = "Mon"

VAR SeasonStartMonth = 6

--Calculation--

VAR SeasonFirstDay =

IF(

MONTH('Date Table'[Date]) < SeasonStartMonth,

DATE(

YEAR('Date Table'[Date])-1,

SeasonStartMonth,

20

),

DATE(

YEAR('Date Table'[Date]),

SeasonStartMonth,

20

)

)

VAR FilteredTableCount =

COUNTROWS(

FILTER(

SELECTCOLUMNS(

GENERATESERIES(

SeasonFirstDay,

'Date Table'[Date]

),

"Dates",

[Value]

),

FORMAT([Dates],"ddd") = WeekStartsOn

)

)

VAR WeekNos =

IF(

FORMAT(SeasonFirstDay,"ddd") <> WeekStartsOn,

FilteredTableCount + 1,

FilteredTableCount

)

RETURN

WeekNos

Example data is below. Over the course of the week, player A covered 28km, whereas player B covered 26km. On Power BI when I try to get the average of distance covered by the players in this week, the average should be 27km. Instead, Power BI is giving me an average of the distance covered in each day of the week, so between 3-4km in this example. 

Day of Week	Distance Covered
Monday	Player A = 4km, Player B = 3km
Tuesday	Player A = 6km, Player B = 5km
Wednesday	Both 0m
Thursday	Player A = 5km, Player B = 4km
Friday	Player A = 3km, Player B = 4km
Saturday	Player A = 10km, Player B = 10km
Sunday	Both 0m

[Ashish_Mathur]

I am sure someone who can understand your reply will help you.