Slicer using filtered Hierarchy

[Ankh-Morpork]

Hi, I have some Survey data in which  i have Organisational Levels 1 to 4. When responding to the survey respondents would select where they are on the organisation levels and their response would be counted in there. 

I created a Hierarchy using the Org Levels 1-4

I want to only show data which has >= 10 responses. But for the slicer functionality, i need it to be a rolled up figure. So for example, the table below may be the response count for some Org groups. I would need the Slicer to showas follows:

Lvl 1: show Team 1 (31 responses) & Team 2 (20 responses)

Lvl 2: show Group 2 (11 responses) & Group 3 (15 responses) & Group 4 (20 responses)

Lvl 3: show Team A (11 responses) & Team B (20 responses)

Lvl 4: Show Division C (20 responses)

Lvl 1	Lvl 2	Lvl 3	Lvl 4	Responses
Team 1	Group 1			5
Team 1	Group 2	Team A	Division A	7
Team 1	Group 2	Team A	Division B	4
Team 1	Group 3			15
Team 2	Group 4	Teram B	Division C	20

• Team 1
  • Group 2
    • Team A
  • Group 3
• Team 2
  • Group 4
    • Team B
      • Division C

Currently everything i have tried, where i would filter out or look to remove Division A and Division B but keep Team A which would meet the >=10 criteria, it still removes the Team A option as well from the slicer.

I would welcome any help/advice on this.

[Anonymous · Accepted solution]

Hi @Ankh-Morpork ,
According to your description, you can either create a new table or create columns on the original table following the logic of this table creation

Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[Lvl 1],
    'Table'[Lvl 2],
    'Table'[Lvl 3],
    'Table'[Lvl 4],
    "Level 1",
    IF(
        CALCULATE(
            SUM('Table'[Responses]),
            ALLEXCEPT(
                'Table',
                'Table'[Lvl 1]
            )
        ) >= 10,
        'Table'[Lvl 1],
        BLANK()
    ),
    "Leve 2",
    IF(
        CALCULATE(
            SUM('Table'[Responses]),
            ALLEXCEPT(
                'Table',
                'Table'[Lvl 1],
                'Table'[Lvl 2]
            ) 
        )>= 10,
        'Table'[Lvl 2],
        BLANK()
    ),
    "Level 3",
    IF(
        CALCULATE(
            SUM('Table'[Responses]),
            ALLEXCEPT(
                'Table',
                'Table'[Lvl 1],
                'Table'[Lvl 2],
                'Table'[Lvl 3]
            ) 
        )>= 10,
        'Table'[Lvl 3],
        BLANK()
    ),
    "Level 4",
    IF(
        CALCULATE(
            SUM('Table'[Responses]),
            ALLEXCEPT(
                'Table',
                'Table'[Lvl 1],
                'Table'[Lvl 2],
                'Table'[Lvl 3],
                'Table'[Lvl 4]
            ) 
        )>= 10,
        'Table'[Lvl 4],
        BLANK()
    )
)

Setting is not blank in level 3 page filters
Final output

Best regards,
Albert He

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

[Ankh-Morpork]

Thanks! I have added this in and it worked well. I get a (Blank) in the hierarchy dropdown if the sub categories dont add up to ten individually but there are enough that together they add up to 10. But that works for me.

Cheers!