Predictions based on past performance

[Anonymous]

I have a data set containing player performance for the last year, which looks like below. In this sport, assume there are 3 types of matches, so the "Type" indicates which type it is. I've taken X, Y, and Z as 3 different types.

Player,Team,Matches,Points,Type
Player A,Australia,25,500,X
Player B,Sri Lanka,23,433,X
Player C,India,24,535,X
Player D,Australia,22,233,X
Player E,England,26,635,X
Player F,Sri Lanka,27,344,X
Player A,Australia,12,150,Y
Player B,Sri Lanka,9,233,Y
Player C,India,11,144,Y
Player D,Australia,8,178,Y
Player E,England,7,201,Y
Player F,Sri Lanka,13,211,Y
Player A,Australia,15,225,Z
Player B,Sri Lanka,18,200,Z
Player C,India,9,136,Z
Player D,Australia,14,253,Z
Player E,England,16,233,Z
Player F,Sri Lanka,18,198,Z

Next, I have a schedule for the next year for each team that displays the number of matches the teams will be playing in each type of matches.

Team,X,Y,Z
Australia,25,9,15
England,29,10,18
India,31,13,19
Sri Lanka,27,11,20

I can now calculate a Points / Match average for each player for each type of match, for the lats year. So the calculated PowerBI table would look like this:

Now, I want to calculate or predict how many points each player will score next year in each type of mathces. Essentially, take the Points/Match for any player for the last year, and then multiply that by the number of matches next year for that player's team (assuming that player will play all matches for that team).

For instance let's take [ Player F ] for [ Type Z ].

Last year Player F scored at 11.00 points per match in Type Z.

Player F's team is Sri Lanka.

Next year, Sri Lanka will play 20 Type Z matches.

So, we can predict that Player F will score 11 x 20 = 220 points in Type Z matches next year.

How can I generate this as a table or matrix, showing each player's predicted performance for the next year in each type of matches?

[Greg_Deckler]

@Anonymous Assuming that you have that as a table visualization? First, you will need to unpivot your type columns (X, Y and Z) in your future matches table and then maybe a measure like:

Measure Total Points =
  VAR __Team = MAX('Table'[Team])
  VAR __Type = MAX('Table'[Type])
  VAR __PointsPerMatch = [Points/Match] //assumes a measure
  VAR __TotalMatches = MAXX(FILTER('MatchesTableNextYear',[Team]=__Team && [Attribute]=__Type),[Value])
RETURN
  __PointsPerMatch * __TotalMatches

[Anonymous]

@Greg_Deckler , thanks for the reply.

This seem to work if I then display this on a table with 'Type' as a column:

The calculated values are fine.

However, if I take out 'Type' column (ultimately I want to know each player's total predicted points), then the summation seems to give me an incorrect value:

For instance PlayerA's total should be 500 + 112.5 + 225 = 837.50.

The resulting value, 252.40, doesn't even seem to be the average (which should be 837.5 / 3 = 279.16), so wonder what's happening here.

[Greg_Deckler]

@Anonymous So what about this?

Measure Total Points =
  VAR __Player = MAX('Table'[Player])
  VAR __Team = MAX('Table'[Team])
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[Type]),
        "__Team",__Team,
        "__PPM",[Points/Match]),
        "__FutureGames", MAXX(FILTER('MatchesTableNextYear',[Team]=__Team && [Attribute]=[Type]),[Value]),
      ),
      "__Points",[__PPM] * [__FutureGames]
    )
RETURN
  SUMX(__Table,[__Points])

[Anonymous]

@Greg_Deckler  This doesn't seem to be a valid formula, starting at Line 11.

[Greg_Deckler · Accepted solution]

@Anonymous Yeah, you'll have syntax errors when you wing it, there was an offending comma. Try:

Measure Total Points =
  VAR __Player = MAX('Table'[Player])
  VAR __Team = MAX('Table'[Team])
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[Type]),
        "__Team",__Team,
        "__PPM",[Points/Match]),
        "__FutureGames", MAXX(FILTER('MatchesTableNextYear',[Team]=__Team && [Attribute]=[Type]),[Value])
      ),
      "__Points",[__PPM] * [__FutureGames]
    )
RETURN
  SUMX(__Table,[__Points])

It's that last comma just above the first line you get the squilly red line under.

[Anonymous]

Worked perfect, thanks!