Find Max for 2 different columns against two criteria, and then perform calculation

[NSlater1]

Hi - I am struggling to create in DAX what should be a simple measure that will take the following sample "raw" data and return the outcome underneath. 

My aspiration is, for each employee, and against each competence, show the Max value for

1) Requirement and

2) Attained,

- and then also return a calculated difference between Requirement and Attained. 

Any help gratefully received 

Data sample:

Employee Number	Competence Cat	Requirement	Attained
12345	1	3	0
12345	1	0	4
12345	10	3	0
12345	10	0	2
54321	2	3	0
54321	2	0	3
54321	11	2	0
54321	11	0	0
54321	11	0	3

Outcome sought

Employee Number	Competence Cat	Requirement	Attained	Competence Gap
12345	1	3	4	1
12345	10	3	2	-1
54321	2	3	3	0
54321	11	2	3	1

[krishnakanth240]

Hi @NSlater1 

Ensure dont summarize for these columns - Employee Number, Competence Cat

[cengizhanarslan · Accepted solution]

You can do this with three measures.

Max Requirement =
MAX ( 'Table'[Requirement] )

Max Attained =
MAX ( 'Table'[Attained] )

Competence Gap =
[Max Attained] - [Max Requirement]

[NSlater1]

Thank you, worked fine- appreciated !