Forum Discussion
Hierachical rollup
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.
- Anonymous - Have you tried the pattern found at https://www.daxpatterns.com/parent-child-hierarchies/ ? You'll likely want to reference your data table keeping only [UserID] & [ManagerID] to make your 'hierarchyTable'. I mocked up a file for you however my reply message no longer shows any features like adding attachments or pictures , etc.
4 Replies
- Anonymous - Have you tried the pattern found at https://www.daxpatterns.com/parent-child-hierarchies/ ? You'll likely want to reference your data table keeping only [UserID] & [ManagerID] to make your 'hierarchyTable'. I mocked up a file for you however my reply message no longer shows any features like adding attachments or pictures , etc.
Anonymous,
By the way, you may share the solution here once it is solved. Your contribution is highly appreciated.
