Create a Max measure that doesn't change

[braja1718]

I'm trying to create a report for my fantasy baseball league. I have a table containing players, whose team they are on or if they are free agents, and then their projected points for the year. I want to create a measure that looks for the Maximum "projected points" out of all the players who are free agents. I then want to create a new column that subtracts this measure from the "projected points" column so I can see how much each player perform above or below the level of the best available free agent.

I've tried:

Measure = Calculate(MAX(Table[projected points]), Table[Team]="free agent")

which works to find the points for the highest free agent.

However, when I try to use that measure, like:

Column = Table[projected points] - Measure

It seems to try to calculate the Measure for each individual player and use that.

So, for players who aren't free agents, the Measure calculates as 0. For players who are free agents, it calculates as that individual player's points value. So the subtraction results in no change for players who aren't free agents, and 0 for players who are. I am struggling to figure out how to create a measure that will stay constant

[amitchandak]

@braja1718 , either create the first one as a column too or create the second one as a measure.

The measure can take slicer value and dynamic , columns are not

Column 1 = Calculate(MAX(Table[projected points]), Table[Team]="free agent")

Column 2= Table[projected points] - Measure

or

Measure = Calculate(MAX(Table[projected points]), Table[Team]="free agent")

Measure 2= Sumx(Table, Table[projected points] - [Measure])

or

Measure 2= Sumx(Table, calculate(sum(Table[projected points]) - [Measure]) )

[braja1718]

I was able to figure out a quick work around by creating a column that returned the "projected points" for players whose teams value was Free Agents, and would return 0 for anyone else. Then created another column the returned the Max of the previous column, thus only including Free Agents. Then I can do my subtraction.

If you can think of a more concise way to get this done, I'd love to hear it! But I have something that works for now.

[braja1718]

Thank you for your reply! Unfortunately, trying those didn't seem to change the issue where the value is calculating the Max for each individual player. Even when I add it as a column. I want it to take the Max of all players who are Free Agents and applying that to all players in the list. 

If I'm willing to make a dedicated column to hold that value, I think it would be best to make that in the Power Query Editor rather than with DAX expressions. However, I'm still struggling with the syntax I'd use to get the Max of just the free agents. I think I could make a separate table for just the free agents and work with that, but I'd rather not have to do that if possible to figure out how to make it work inside the function.

Just to clarify, here's the idea:

Player Name	Team	Projected Points	Max Free Agent	Difference
Player 1	Team 2	350	200	150
Player 2	Team 4	320	200	120
Player 3	Team 1	250	200	50
Player 4	Free Agent	200	200	0
Player 5	Team 3	190	200	-10
Player 6	Free Agent	150	200	-50
Player 7	Free Agent	150	200	-50

See that Player 4 is the Free Agent with the highest Project Points total, at 200. Then I put that value into a column and subtract it from everyone.