Cluster Bar Chart and Hierarchy

[chrisx]

Hello all,

I have built a clustered bar chart that looks great. The different columns are regions in a data set I am working with. I am having an issue with the slicer I am using.

I am using a hierarchy in the slicer. I'd like to drill down into the different levels of the hierarchy . But when I do so, I do not get the clusters of the next level of the hierarchy.

When I enter the hierarchy into the 'Legend' section, only the top level of the hierarchy is entered. And it goves me one bar as pictured below, instead of the cluster I am looking for.

Any ideas on how I could correct this?

Thank you!

[Susanta91]

Great work! @MFelix . Using the same technique and bit changes in measure we can use dynamic legend as well like Parker have shared the technique in his YouTube channel @Bielite and blog post. However, your perception is also applicable. Keep doing some extraordinary job like this.

[MFelix]

Hi @Susanta91 ,

Currently with the Field parameters, this is even easier to do without the need of disconnected tables. but appreciate your words.

[v-yangliu-msft]

Hi  @chrisx ,

You need to click the following icon to drill down and then select the slicer.

Then you can get the visual object of the hierarchical structure:

Best Regards,

Liu Yang

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

[MFelix]

Hi @chrisx ,

If your cluster in the bar chart is the region then when you use the slicer you get only a single column because the slicer and the bar chart have the same level of granularity, if you want to show the details of the Subregion then you need to do that on the bar chart.

One question do you want to have a dinamic change of the bar chart according to the level of the hierarchy slicer you select, so when selecting region you get the bar chart divided by Subregion?

[chrisx]

Thanks, Miguel.

Yes, I would ike the dynamic change in the bar chart according to the level of hierarchy.

[MFelix · Accepted solution]

Hi @chrisx ,

You need to make a unrelated table and a measure to make this work.

Create the following table:

Regions =
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Table'[Region] ), "Category", "Region" ),
    ADDCOLUMNS ( DISTINCT ( 'Table'[SubRegion] ), "Category", "SubRegion" ),
    ADDCOLUMNS ( DISTINCT ( 'Table'[Office] ), "Category", "Office" )
)

Add the following measure to your model:

Hierarchy Values =
SWITCH (
    TRUE (),
    HASONEVALUE ( 'Table'[SubRegion] ),
        CALCULATE (
            SUM ( 'Table'[Values] ),
            'Table'[Office] = MAX ( 'Regions'[Region] )
        ),
    HASONEVALUE ( 'Table'[Region] ),
        CALCULATE (
            SUM ( 'Table'[Values] ),
            'Table'[SubRegion] = MAX ( 'Regions'[Region] )
        ),
    CALCULATE (
        SUM ( 'Table'[Values] ),
        'Table'[Region] = MAX ( 'Regions'[Region] )
    )
)

Now setup your chart in the following way:

• Axis: Regions[Region]
• Legend: Table[Cat]
• Values: [Hierarchy Values]

Check PBIX file attach.