Subtracting the highest value for each value below to find the difference for each

[PauloRicardo]

Hi guys,

  In the same job position i have different salary, i already found the highest value (i used MAX). Now i need help finding how to subtract the highest value for each salary below to find the difference.

Role	Name	Female Salary	Male Salary	Goal
Administrative	Julia	2.350		0
Administrative	Robert		2.150	200
Administrative	John		2.200	150
Administrative	Sandra	2.100		250
Total	---	---	---	600

Any help?

[ryan_mayu · Accepted solution]

@PauloRicardo 

select role and name column , and unpivot other column in PQ

then use DAX to create a column

Column = max('Table'[Value])-'Table'[Value]

pls see the attachment below

[v-tangjie-msft · Accepted solution]

Hi @PauloRicardo ,

Thanks @ryan_mayu  for the quick reply and solution.

I have some other ideas for you to refer. We  can create a calculated column in Desktop.

SalaryGoal = 
var _1=MAX('Table'[Female Salary])
var _2=MAX('Table'[Male Salary])
var _max=IF(_1>=_2,_1,_2)
var _salary=[Female Salary]+[Male Salary]
RETURN _max-_salary

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ryan_mayu · Accepted solution]

@PauloRicardo 

select role and name column , and unpivot other column in PQ

then use DAX to create a column

Column = max('Table'[Value])-'Table'[Value]

pls see the attachment below