Forum Discussion

ThomasRichard21's avatar
ThomasRichard21
Frequent Visitor
2 years ago

Weekly Totals per player

Hi all,

 

I have to do something quite simple, but cannot find the right way to do it. My data base contains football GPS Data, with a row corresponding for a date, a player, and activity, and thousands of metrics.

I added a Week column and would like to have a weekly average for each player, and do further calculations with these values (for example the average of all the totals for a given week).

 

I tried different things:

 

1) Doing the average of the whole team directly in a visual, for each week, but it comes out as the average of each day instead of the average of the week total.

 

2) Created a calculated column with weekly totals. The problem with that is that the weekly total is shown on each row of my table, so the average between all players depends on how many rows the player got that week. This is the formula I used:

Total semaine TD = CALCULATE(SUM('ARaw'[Total Distance]), FILTER(ARaw,ARaw[Nom]=earlier(ARaw[Nom]) && ARaw[Semaine]=earlier(ARaw[Semaine])), ARaw[Period Name]="Terrain" || ARaw[Period Name]="Match" || ARaw[Period Name]="Reha" || ARaw[Period Name]="Modifié" || ARaw[Period Name]="Récup")
 
"Araw" is my main table, [Total Distance] is the metric I want to summarize, [Nom] is my players name, [Semaine] is my Week number, and all [Period Name] filters is a filter of activity because I don't want to include all rows (all activities).
 
3) To resolve that I tried to create a Measure. The problem with my measure is that I cannot aggregate as average in the visual. It can only show player by player, or if I put the whole team it will only sum up the values.
Mesure Total semaine TD = CALCULATE(SUM('ARaw'[Total Distance]), FILTER(ARaw,ARaw[Nom]<=MAX(ARaw[Nom]) && ARaw[Semaine]<=MAX(ARaw[Semaine])), ARaw[Period Name]="Terrain" || ARaw[Period Name]="Match" || ARaw[Period Name]="Reha" || ARaw[Period Name]="Modifié" || ARaw[Period Name]="Récup")
 
4) Finally I created a summarized table. The table shows well the players in one column, the week in the 2nd, and their total in the 3rd. The problem is that I don't know how to link it properly to my main table. The week numbers and names don't seem to be properly linked because there is many on one side, and many on the other.
Summarized = CALCULATETABLE(SUMMARIZE(ARaw,ARaw[Nom],ARaw[Semaine],"Total Semaine TD",SUM(ARaw[Total Distance])),FILTER(ARaw,ARaw[Period Name]="Terrain"||ARaw[Period Name]="Reha"||ARaw[Period Name]="Modifié"||ARaw[Period Name]="Récup"))
 
My last idea is to create a summarized table with one different column for each week, to have only one row for each player. But it doesn't seem very efficient for such a normal calculation to create.
 
Any tips?
 
Thanks a lot in advance,
 
Thomas
Need Help

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello ThomasRichard21,

     

    Can you please try the following approach:

     

    1. Calculate the weekly total distance for each player

    WeeklyTotalDistance = 
CALCULATE(
    SUM(ARaw[Total Distance]),
    FILTER(
        ARaw,
        ARaw[Nom] = MAX(ARaw[Nom]) && ARaw[Semaine] = MAX(ARaw[Semaine]) &&
        (ARaw[Period Name] = "Terrain" || ARaw[Period Name] = "Match" || ARaw[Period Name] = "Reha" || ARaw[Period Name] = "Modifié" || ARaw[Period Name] = "Récup")
    )
)

    2. Calculate the average weekly total distance per player

    WeeklyAverageDistance = 
CALCULATE(
    AVERAGEX(
        VALUES(ARaw[Semaine]),
        [WeeklyTotalDistance]
    )
)

    3. Calculate the average of all players' weekly totals for a given week

    OverallWeeklyAverage = 
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            ARaw,
            ARaw[Nom],
            ARaw[Semaine],
            "WeeklyTotalDistance", [WeeklyTotalDistance]
        ),
        [WeeklyTotalDistance]
    ),
    ALLSELECTED(ARaw[Semaine])
)

    You should also have a relationship between your player names and week numbers in both tables.

     

    Hope this helps!

    • ThomasRichard21's avatar
      ThomasRichard21
      Frequent Visitor
      2 years ago

      Hi Sahir_Maharaj ,

       

      Thanks a lot for the fast answer.

       

      Should they all be "New Measures"? 

      Regarding your 2nd Measure "WeeklyAverageDistance", I am not sure I understand what I would measure with that, but I did it and it seems to give me the same thing than the first one. also where do you use it in your 3rd suggested measure?

       

      Regarding the last one OverallWeeklyAverage, it doesn't seem to give me the right result.

      Also I don't understand from which tables I should match the players and week numbers? Should I create a new table at some point?

       

      Thanks a lot again

       

      Thomas