Forum Discussion

nunnc01's avatar
nunnc01
Helper II
5 years ago
Solved

Dynamic Hierarchy Level in Visuals

Hi all

 

I have a load of bar/column chart visuals that show information split by the 5 levels of the hierachy that I have to report on (from top to bottom) - Division, Area, Service, Team and Worker. Using the drillup and drilldown arrows allows people to view the information at the level that they are interested in.

 

What I would like is for them to initally select from a slicer the Division, Area, Service or Team that they are interested in and for all the graphs to then automatically show the next level of information down. So if the person selects a Service, I would like the graphs to automatically show the data at Team Level (for the service they have selected).  If they select Team it would show Worker level etc.

 

I can not think of a way to achieve this but hoping that somone might have a clever idea as to how it can done.

 

Any help greatly appreciated

 

Thanks

 

Chris

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi nunnc01 ,

     

    This can be achieved using two disconnected tables and a measure with a switch formula:

     

    • Add the following code for two tables:
    Slicer =
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Area] ),
        "Type", "Area",
        "Group Level", "Division"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Dvision] ),
        "Type", "Division",
        "Group Level", "Service"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Service] ),
        "Type", "Service",
        "Group Level", "Team"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Team] ),
        "Type", "Team",
        "Group Level", "NODETAIL"
    )
)



X Axis Values =
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Area] ),
        "Type", "Area",
        "Group Level", "Division"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Dvision] ),
        "Type", "Division",
        "Group Level", "Service"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Service] ),
        "Type", "Service",
        "Group Level", "Team"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Team] ),
        "Type", "Team",
        "Group Level", "NODETAIL"
    )
)

     

    • For your values add the following measure:
    Values By next level =
SWITCH (
    SELECTEDVALUE ( Slicer[Type] ),
    "Division",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Area], 'Table'[Dvision] ),
                'Table'[Area]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Dvision] IN VALUES ( Slicer[Area] )
            )
        ),
    "Area",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Service], 'Table'[Area] ),
                'Table'[Service]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Area] IN VALUES ( Slicer[Area] )
            )
        ),
    "Service",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Team], 'Table'[Service] ),
                'Table'[Team]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Service] IN VALUES ( Slicer[Area] )
            )
        )
)

     

    • Now use the slicer talbe for your slicer and the X-Axis for your axis on the chart on the values place the measure result below and  in attach file

    I finish the setup at Team level but you can setup this to multiple leves just need to add it to the measure.

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi nunnc01 ,

     

    This can be achieved using two disconnected tables and a measure with a switch formula:

     

    • Add the following code for two tables:
    Slicer =
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Area] ),
        "Type", "Area",
        "Group Level", "Division"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Dvision] ),
        "Type", "Division",
        "Group Level", "Service"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Service] ),
        "Type", "Service",
        "Group Level", "Team"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Team] ),
        "Type", "Team",
        "Group Level", "NODETAIL"
    )
)



X Axis Values =
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Area] ),
        "Type", "Area",
        "Group Level", "Division"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Dvision] ),
        "Type", "Division",
        "Group Level", "Service"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Service] ),
        "Type", "Service",
        "Group Level", "Team"
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Team] ),
        "Type", "Team",
        "Group Level", "NODETAIL"
    )
)

     

    • For your values add the following measure:
    Values By next level =
SWITCH (
    SELECTEDVALUE ( Slicer[Type] ),
    "Division",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Area], 'Table'[Dvision] ),
                'Table'[Area]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Dvision] IN VALUES ( Slicer[Area] )
            )
        ),
    "Area",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Service], 'Table'[Area] ),
                'Table'[Service]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Area] IN VALUES ( Slicer[Area] )
            )
        ),
    "Service",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Team], 'Table'[Service] ),
                'Table'[Team]
                    IN VALUES ( 'X-Axis Values'[Area] )
                        && 'Table'[Service] IN VALUES ( Slicer[Area] )
            )
        )
)

     

    • Now use the slicer talbe for your slicer and the X-Axis for your axis on the chart on the values place the measure result below and  in attach file

    I finish the setup at Team level but you can setup this to multiple leves just need to add it to the measure.