cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sach
Helper III
Helper III

Predictions based on past performance

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:

 

Table.png

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?

1 ACCEPTED SOLUTION

@Sach 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Sach 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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler , thanks for the reply.

 

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

 

A.png

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:

B.png

 

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.

 

 

 

@Sach 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])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

 

formula.png

@Sach 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Worked perfect, thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.