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 tha...
General Comment
Interesting Links
Need Help
Tutorial Requests
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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

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