Dax calculation help

[Anonymous]

Hello, 

I need somehelp on Dax calulation. Below is the sample data:

I have dataset like below. 

Department	Grade	No of Emp
IT	A	90
IT	B	90
IT	C	90
HR	A	10
HR	B	5
HR	C	1
NonIT	A	4
NonIT	B	16
NonIT	C	0
Security	A	5
Security	B	3
Security	C	2

Required output is below:

Department	No of Emp
IT	90
HR	16
NonIT	20
Security	10

Only for 'IT' department i dont want to do the sum of 'No of Emp' column but for all other departments I want to do the sum on 'No of Emp' and display the value. As you can see for IT the value is same 90 in source dataset and output table but for HR in the output table it is 20 (10+5+1). 

Can someone please advice how I can get this. Please let me know if I am not clear.

Thank you.

[PattemManohar]

@Anonymous  Please try this as a New Table.

Test288Out = SUMMARIZE(Test288SumIF,Test288SumIF[Department],"Count",IF(Test288SumIF[Department]="IT",MAX(Test288SumIF[NoOfEmp]),SUM(Test288SumIF[NoOfEmp])))

[Anonymous]

Hi @PattemManohar ,

Thank you its working but the other thing I would like to add is I want to show 'Department' and 'Grade' as slicers. So when the user select Department = IT and Grade = All then it should show 90 and even if the user select a particular grade lets say 'A' and department = IT it should show 90 only. Any chance its possible please? 

Many Thanks

[PattemManohar · Accepted solution]

@Anonymous  In that case, create a new measure as below

Test288 = 
VAR _IT = MAXX(FILTER(Test288SumIF,Test288SumIF[Department]="IT"),Test288SumIF[NoOfEmp])
VAR _Other = SUMX(Test288SumIF,Test288SumIF[NoOfEmp])
RETURN IF(SELECTEDVALUE(Test288SumIF[Department])="IT",_IT,_Other)

[Anonymous]

Thanks alot @PattemManohar . Its working perfectly.