cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syale Frequent Visitor
Frequent Visitor

Hierachical rollup

hierachyI need to go from this:

 

PhoneUserIDManagerIDCost
(111) 111-11111  $    21.00
(111) 111-111221 $    25.00
(111) 111-111331 $    24.00
(111) 111-111441 $    27.00
(111) 111-111551 $    21.00
(111) 111-111662 $    24.00
(111) 111-111772 $    21.00
(111) 111-111882 $    25.00
(111) 111-111993 $    24.00
(111) 111-1120103 $    27.00
(111) 111-1121113 $    21.00
(111) 111-1122124 $    24.00
(111) 111-1123134 $    21.00
(111) 111-1124144 $    25.00
(111) 111-1125157 $    24.00
(111) 111-1126167 $    27.00
(111) 111-1127178 $    21.00
(111) 111-11281810 $    24.00
(111) 111-11291910 $    21.00
(111) 111-11302010 $    25.00

 

to a hiearchical rollup 

 

L0L0CostL0RollUpL1L1CostL1RollUpL2L2CostL2RollUpL3L3CostL3RollUp
            
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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisMendoza Established Member
Established Member

Re: Hierachical rollup

@syale - 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.
syale Frequent Visitor
Frequent Visitor

Re: Hierachical rollup

Thanks I will give it a try Smiley Happy Looks promising!

4 REPLIES 4
ChrisMendoza Established Member
Established Member

Re: Hierachical rollup

@syale - 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.
syale Frequent Visitor
Frequent Visitor

Re: Hierachical rollup

Thanks I will give it a try Smiley Happy Looks promising!

Community Support Team
Community Support Team

Re: Hierachical rollup

@syale,

 

By the way, you may share the solution here once it is solved. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
syale Frequent Visitor
Frequent Visitor

Re: Hierachical rollup

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