Forum Discussion
Dax calculation help
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.
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)
4 Replies
Anonymous Please try this as a New Table.
Test288Out = SUMMARIZE(Test288SumIF,Test288SumIF[Department],"Count",IF(Test288SumIF[Department]="IT",MAX(Test288SumIF[NoOfEmp]),SUM(Test288SumIF[NoOfEmp])))
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
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)