Matrix Hierarchy Roll Up Issues

[AreJayIII]

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.

[Anonymous]

Hi, @AreJayIII 

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.

[AreJayIII]

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 😕

[Ritaf1983]

Hi @AreJayIII 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...