Creating a normalized score for intersection of two measures

Question

Let me start by apologizing for bringing very little to the table with this problem and asking for help. I am moderate with DAX and could probably figure out the syntax to calculate the measure but I am stuck on how to structure the problem logically. I do not have a background in statistics and if I did I think this would be a no brainer. Lets use the golfing scenario below as an anology

We have golfers . Thier score is recorded (strokes) on each hole and totaled for the round. Players also earn points on each hole and the number of points depends on how well they did on that hole. Points are also totaled for the round. Low scores are good and high points are good, but in the end it is points that really matter, not score (low strokes).

There is high negative correlation between Score and Points. Usually (but not always exactly linear) lower scores means higher points in aggregate over all 18 holes. A good example of this would be a golfer has a couple of blowup holes where he records a lot stokes (thus greatly increasing his score and getting zero points), but does very well on the other holes and earns a lot points.

I would like to assign a normalized score between 0 an 1 to each golfer that looks at how well they did in terms of the relationship between score and points.

Sample data:

Golfer A: Score = 72 Points = 12

Golfer B: Score = 72 Points = 10

Golfer C: Score = 80 Points = 10

I am not even sure what to call the measure I am trying to create, but I am trying to figure out a measure that expresses

Golfer A did better than Golfer B: Golfer A earned more points than B but shot the same score

Golfer C did better than Golfer B: Golfer C earned the same amount of points as B but with a higher score

I can put this data into a scatter chart with a trend line and visually see who did well with points vs the score, but I would like to express this as measure between 1 and 0. Thanks for the help

parry2k · Answer

Georgia_Mike&nbsp;I guess first and foremost you will select a golfer and then compare it with other golfers and based on the selected golfer, you will assign 0 and 1, is that what you are thinking?

georgia_mike · Answer

Thank you for the response.&nbsp;That is in the ballpark but not quite.&nbsp; I am trying to figure out a way to numerically score each golfer between 0 and 1 based on how many points they earned interpolated with the number of strokes they recorded.&nbsp;&nbsp;In the example above A would score higher on the 0 to 1 scale than B, and C would score higher on the 0 to 1 scale than B.&nbsp; &nbsp; In prose, "A did more than B with the same" and&nbsp; "C did the same than B with less"&nbsp; (that can get confusing because a higher score is less desirable, remember higher points are good and higher scores are bad)&nbsp;If it helps here is how it would be applied:&nbsp;Lets imagine we do this calculated measure over many rounds worth of data and this measure is available to you for each golfer.&nbsp; &nbsp;You are given to given the option to choose 1 of 2 golfers as your partner, lets call tehm A &amp; B.&nbsp;Golfer A &amp; B always earn same amount of points but A usually does it with a higher score.&nbsp; &nbsp; A is more deisiable partner than B because historically A has more room for error (higher score/strokes) than B&nbsp;OR&nbsp;&nbsp;Golfer A &amp; B always score (strokes) the same but A usually earns more points.&nbsp; &nbsp; A is a more deisrable partner than B&nbsp;&nbsp;&nbsp;I could kluge a ranking by golfer, but a rank is really not what Im looking for.&nbsp; &nbsp;Im trying to define the logic for a unified score.&nbsp; In school, a 93 and a 99 are both As, but the 99 is the better A

parry2k · Answer

Georgia_Mike&nbsp;why you are will compare A with B and not with C that is not clear? One golfer only get compare with one another golfer, if yes then what is the logic?

georgia_mike · Answer

parry2k&nbsp;Im sorry I am not explaining this very well.&nbsp; Let me post some sample data and also a visual try to explain better.&nbsp; Here is some sample data:&nbsp;&nbsp;This is data for about 7 rounds of golf for 8 players with a SUMX on both Score and Points.&nbsp; I also have a correlation coefficient that calculates across the sliced set by Player.&nbsp; As you can see from the correlation values&nbsp; I have players whose score correlates to thier points at varying degees, from a min of .82 to a max of .96.&nbsp; Now keep in mind those with lower correlations in the .80s, the correlation values don't really tell you if the drift from the regression is good or bad.&nbsp; &nbsp;The Players with a correlation very close to 1 are earning points almost exacly like you would expect.&nbsp; These are good golfers who are consistent and do not have a lot of holes way above or below what you would expect (Par).&nbsp; Those with lower correlations have more holes away from the average, which could be huge variations on a smaller amout of holes or lesser variances but a lot of them.&nbsp; These variance could be good or bad.&nbsp;&nbsp;&nbsp;The Points/Score measure is not very useful.&nbsp; it doesnt answer the question of did the player earn more or less points than average for the number of strokes incurred without any kind of normalization to the entire set.&nbsp; .4 in this calc may or may not be twice as good as .2&nbsp;What I am trying to do is to determine if that Points/Score ratio is good or bad compared to all players in the set......and then to normalize it on standard scale (0 to 1 or 1 to 100) so that comparison between players is meaningful, i.e. the difference between .8 to .85 would have the same weight as the difference between .85 and .9.&nbsp;Take a look at this data plotted into a Scatter Chart below with a trend line.&nbsp; From this picture I can see which players are earning more total points on average than average based on their thier total score.&nbsp; The reverse is also shown, I see where players are earning less points than average based on thier total score.&nbsp;&nbsp;This what I am trying to quantify in a single measure with a min of 0, a max of 1, with .5 being the trend line.&nbsp; &nbsp;This value in essence would measure a players drift from the trend line in the Scatter Chart, Being above the regression line is "good" and below the regression line is "bad".&nbsp; In the calculated measure Good would be above .5 and Bad would be below .5.&nbsp;Its a stats equation but I don't know conceptually how to do the stats.&nbsp; If I knew the formula I could write the DAX.&nbsp;Thanks again for taking the time&nbsp;