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

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.