Forum Discussion
Create a Max measure that doesn't change
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")
3 Replies
- amitchandak
Super User
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]) )
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.
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.
