Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
Proud to be a PBI Community Champion
@Anonymous Please try this as a New Table.
Test288Out = SUMMARIZE(Test288SumIF,Test288SumIF[Department],"Count",IF(Test288SumIF[Department]="IT",MAX(Test288SumIF[NoOfEmp]),SUM(Test288SumIF[NoOfEmp])))
Proud to be a PBI Community Champion
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)
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |