Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Getting the average for summed weekly data

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

General Comment
Interesting Links
Need Help
Tutorial Requests

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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 WeekDistance Covered
      MondayPlayer A = 4km, Player B = 3km
      TuesdayPlayer A = 6km, Player B = 5km
      WednesdayBoth 0m
      ThursdayPlayer A = 5km, Player B = 4km
      FridayPlayer A = 3km, Player B = 4km
      SaturdayPlayer A = 10km, Player B = 10km
      Sunday

      Both 0m