Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Matrix Hierarchy Roll Up Issues

I am having trouble with my Organizational Hierarchy not rolling up appropriately.

 

When expanded, it shows appropriately:

When you collapse the 4th level of the hierarchy, to sum up to level 3, it sums everything and ignores slicers such as my User Segment:

So basically I am getting the appropriate summing at level 4, but at level 3 the roll up ignores filters and rolls up everything.

 

Here are the measures I am using:

Commit Forecast 2024 = CALCULATE(
    SUM('Forecasting Item'[ForecastAmount]),
    FILTER('Forecasting Item',
    'Forecasting Item'[ForecastingItemCategory]="CommitForecast" &&
    'Forecasting Item'[Forecasting Type]="New Sale Amount" &&
    YEAR('Forecasting Item'[Period Start Date]) = 2024))

Commit Forecast 2024 Switch =

var Level7Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=6)
var Level7Selected = ISINSCOPE('User'[Organizational Level 1])

var Level6Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=5)
var Level6Selected = ISINSCOPE('User'[Organizational Level 2])

var Level5Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=4)
var Level5Selected = ISINSCOPE('User'[Organizational Level 3])

var Level4Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=3)
var Level4Selected = ISINSCOPE('User'[Organizational Level 4])

var Level3Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=2)
var Level3Selected = ISINSCOPE('User'[Organizational Level 5])

var Level2Sum = CALCULATE([Commit Forecast 2024]*[HideAccount], 'User'[Inverse Path]=1)
var Level2Selected = ISINSCOPE('User'[Organizational Level 6])

return     SWITCH(
                TRUE()
                ,Level2Selected,Level2sum
                ,Level3Selected,Level3sum
                ,Level4Selected,Level4sum
                ,Level5Selected,Level5sum
                ,Level6Selected,Level6sum
                ,Level7Selected,Level7Sum
                )
 
HideAccount = if([AccountScope] <= min('User'[Path Length]) ,1)
 
AccountScope =
switch(TRUE(),
ISINSCOPE( 'User'[Organizational Level 6]), 6,
ISINSCOPE( 'User'[Organizational Level 5]), 5,
ISINSCOPE( 'User'[Organizational Level 4]), 4,
ISINSCOPE( 'User'[Organizational Level 3]), 3,
ISINSCOPE( 'User'[Organizational Level 2]), 2,
ISINSCOPE( 'User'[Organizational Level 1]), 1)

the relationship between user and forecasting item is User'Id' (many to one) -> Forecasting Item'OwnerId'
 
Any thoughts would be greatly appreciated.
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Anonymous 

     

    Can you provide example data? And what you expect the output to be. There is sensitive data that can be removed in advance.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I don't think I can provide sample data due to our company policies. 

       

      I have a user table with a Path that goes up to 6 levels of hierarchy. When I expand my matrix the data shows appropriately (see photo one). But when I collapse it to the next level up, it changes to include all sales (photo 2). I have a filter applied that is slicing on Segment from my User table but it is like it ignores filtering. Hopefully that helps because I'm not sure I can provide much more information 😕