Forum Discussion

Ankh-Morpork's avatar
Ankh-Morpork
Frequent Visitor
1 year ago
Solved

Slicer using filtered Hierarchy

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 1Lvl 2Lvl 3Lvl 4Responses
Team 1Group 1  5
Team 1Group 2Team ADivision A7
Team 1Group 2Team ADivision B4
Team 1Group 3  15
Team 2Group 4Teram BDivision C20

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Ankh-Morpork
      Frequent Visitor
      1 year ago

      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!