Forum Discussion

chrisx's avatar
chrisx
Frequent Visitor
4 years ago
Solved

Cluster Bar Chart and Hierarchy

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!

 

 

 

 

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    4 years ago

    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.

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      chrisx
      Frequent Visitor
      4 years ago

      Thanks, Miguel.

       

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

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • Susanta91's avatar
    Susanta91
    Frequent Visitor
    3 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      3 years ago

      Hi Susanta91 ,

       

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