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.
hierachyI need to go from this:
Phone | UserID | ManagerID | Cost |
(111) 111-1111 | 1 | $ 21.00 | |
(111) 111-1112 | 2 | 1 | $ 25.00 |
(111) 111-1113 | 3 | 1 | $ 24.00 |
(111) 111-1114 | 4 | 1 | $ 27.00 |
(111) 111-1115 | 5 | 1 | $ 21.00 |
(111) 111-1116 | 6 | 2 | $ 24.00 |
(111) 111-1117 | 7 | 2 | $ 21.00 |
(111) 111-1118 | 8 | 2 | $ 25.00 |
(111) 111-1119 | 9 | 3 | $ 24.00 |
(111) 111-1120 | 10 | 3 | $ 27.00 |
(111) 111-1121 | 11 | 3 | $ 21.00 |
(111) 111-1122 | 12 | 4 | $ 24.00 |
(111) 111-1123 | 13 | 4 | $ 21.00 |
(111) 111-1124 | 14 | 4 | $ 25.00 |
(111) 111-1125 | 15 | 7 | $ 24.00 |
(111) 111-1126 | 16 | 7 | $ 27.00 |
(111) 111-1127 | 17 | 8 | $ 21.00 |
(111) 111-1128 | 18 | 10 | $ 24.00 |
(111) 111-1129 | 19 | 10 | $ 21.00 |
(111) 111-1130 | 20 | 10 | $ 25.00 |
to a hiearchical rollup
L0 | L0Cost | L0RollUp | L1 | L1Cost | L1RollUp | L2 | L2Cost | L2RollUp | L3 | L3Cost | L3RollUp |
1 | $ 21.00 | $ 21.00 | $ 97.00 | ||||||||
2 | $ 25.00 | $ 70.00 | |||||||||
6 | $ 24.00 | ||||||||||
7 | $ 21.00 | $ 51.00 | |||||||||
15 | $ 24.00 | ||||||||||
16 | $ 27.00 | ||||||||||
8 | $ 25.00 | $ 21.00 | |||||||||
17 | $ 21.00 | ||||||||||
3 | $ 24.00 | $ 72.00 | |||||||||
9 | $ 24.00 | ||||||||||
10 | $ 27.00 | $ 70.00 | |||||||||
18 | $ 24.00 | ||||||||||
19 | $ 21.00 | ||||||||||
20 | $ 25.00 | ||||||||||
11 | $ 21.00 | ||||||||||
4 | $ 27.00 | $ 70.00 | |||||||||
12 | $ 24.00 | ||||||||||
13 | $ 21.00 | ||||||||||
14 | $ 25.00 | ||||||||||
5 | $ 21.00 | ||||||||||
Total | $ 21.00 | $ 97.00 | $ 212.00 | $ 142.00 |
I created a SQL view to give me the levels through outer joins but having difficult getting the cost rollup to work.
Solved! Go to Solution.
Proud to be a Super User!
Proud to be a Super User!
Thanks I will give it a try 🙂 Looks promising!
@Anonymous,
By the way, you may share the solution here once it is solved. Your contribution is highly appreciated.
This solution worked great. Key thing is to ensure all the managers are indeed users and the top manager (CEO) does not have a manager.
Stephen
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 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |