Forum Discussion
Max Value of Calculated Column
Hello All - I have a Performance Management table. The Manager updates the Actual numbers in different columns. Based on the updated numbers updated, there is a calculated column in POwer BI that calculates the weightage score for each parameter. We have 15 such calculated columns.
One of the requirements is to get the max value against the team. For Eg A's score is 10%, B is 22%, C is 30%, In case the A is selected in filter, I would like to see the max value as 30% as that value is highest for the month.
How can I achieve this either in Measure or Power Query.
Anonymous ,
You can get this by removing the filters on the table for the particular column.
For ex:
If you want output a Max Value for the month and irrespective of team,
the you might have to use DAX like shown below as a measure. Here the assumption is you are having 3 columns in your visual, Month, Team, Value
MaxValue = CALCULATE(MAX(Table3[Value]), REMOVEFILTERS(Table3[Team]))If you need more precise answer for your team, suggestion is to always share, some sample dummy data that you use as input and the expected output
Regards,
Hi Anonymous - Create a new measure or Power query editor in your Power BI report that calculates the maximum score across the entire team.
In Power query editor too.
first do a group by in power query editor in your table after that find the list.max.
List.Max(Table.Column([Gp], "Runs"))
or using measure:
MaxTeamScore =
CALCULATE(
MAX('Performance Management'[Score]),
REMOVEFILTERS('Performance Management'[Individual])
)Hope this helps.
5 Replies
Anonymous ,
You can get this by removing the filters on the table for the particular column.
For ex:
If you want output a Max Value for the month and irrespective of team,
the you might have to use DAX like shown below as a measure. Here the assumption is you are having 3 columns in your visual, Month, Team, Value
MaxValue = CALCULATE(MAX(Table3[Value]), REMOVEFILTERS(Table3[Team]))If you need more precise answer for your team, suggestion is to always share, some sample dummy data that you use as input and the expected output
Regards,
Hi Anonymous - Create a new measure or Power query editor in your Power BI report that calculates the maximum score across the entire team.
In Power query editor too.
first do a group by in power query editor in your table after that find the list.max.
List.Max(Table.Column([Gp], "Runs"))
or using measure:
MaxTeamScore =
CALCULATE(
MAX('Performance Management'[Score]),
REMOVEFILTERS('Performance Management'[Individual])
)Hope this helps.
Hi Anonymous Since you have not provided any data structure, I am considering, you have team, score and month. You want to find the maximum score among the team for the month regardless of team selection. You can try this:
MaxScore =
Calculate (
Max(Table[Score]),
ALL(Table[Team])
)
For other scenario, please provide sample data.
Hope this helps!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar HafizI have a follow up on the above. In the solutions given, I see a small hick up, but before that some more info
The Employee Performance Management is accessible only to the ind Employee via RLS, Manager can see the complete team that reports to them, Senior Manager can see the complete team.
The above solution for the Senior Manager is showing accurate data as he is able to see the full team, but when the Employee or his manager is seeing the data in service, the Max value is either the EE's max value and not team's plus the manager is able to see max at his team level.
I would like the EE see the Max based similar to what Sr. Manager sees
