Category Hierarchy for graph legend

[Nathanhdd]

HI, 

I have data as seen in the below image that contains an account type 2 (landfill or recycling) and then waste type 3 (specific waste streams). I also have a date column and the usage. Currently I have it so the date is on the x-axis and the usage on the y axis with the usage as values and account type 2 as the legend. This allows me to see a stacked column chart with landfill and recycling, but the problem is when I want to look at specific waste streams I can't see this in the stacked column chart as it will simply say "recycling" instead of the waste streams. 

Is there a way to have it stack landfill and recycling, but then I can drill down/ filter to the account type 3 and have this as my stacked chart? 

Thanks! 

[Ritaf1983]

Hi @Nathanhdd 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[AllisonKennedy · Accepted solution]

@Nathanhdd 

Unfortunately stacked column charts are limited in their ability. You can only put one field in the 'legend' of a chart, so can't do heirarchy in there. 

A couple options for workarounds:

Option A: Use field Parameters

Field parameters are new-ish in Power BI and let you create a single 'field' the refers to multiple columns. then you can add a slicer on the page to choose whether you want to view by AccountType2 or AccountType3. 

Here I've done it using dates as my parameter: 

You'll see when I change the slicer from 'Year' to 'Month Year', the legend stacking changes automatically. 

In your case, you already have the dates on the X-axis, so you'd use the AccountType fields in your field parameter. 

In the modelling tab in the ribbon, click 'New Parameter' > "Field Parameter" and select your AccountType2 and AccountType3 to add to the parameter, tick the box to add a slicer to this page, then add your newly created parameter to the legend field.

Get fancy: Auto-filter the field parameters

If you want, you can get really fancy and create a measure that automatically selects the AccountType3 when there's only a single value for AccountType2, and add that as a visual level filter to your column chart. Then change your slicer to use AccountType2 instead of the field parameter, and you'll see that if Recycling and Landfill are both selected, the legend will split by Recycling/Landfill. But if you select Recycling only, the legend will split by AccountType3. 

@mention me if you want help writing this measure. 

Option B: Use Custom visual

You could try creating a custom visual that does more what you want. Charticulator and SVG visuals are available, but you'll need to do a lot of upskilling to learn how to use either of these. 

Option C: Use clustered

If you're happy with the account type being clustered instead of stacked, you could put the Account Type in the X-axis after the date, and then you can drill down. 

[Nathanhdd]

Hi @AllisonKennedy thank you so much! I have managed to add the parameter method and it is all working. It would be amazing to get your help in doing the auto filter, this will be going to a pretty wide audience so the easier it can be the better 🙂

Thanks again! 

[AllisonKennedy]

@Nathanhdd Yay! So glad you got it working.

If you can provide your parameter name this might be easier, but below is an example of how I've done this with dates: let's assume you have a parameter called "Date Field Parameter" (I know your parameter will be for AccountType not date, but similar concept applies). This parameter should have a table, with at least two columns:

• 'Date Field Parameter'[Date Field Parameter]
• 'Date Field Parameter'[Date Field Parameter Order]

In the measure below, you'll see the idea is to figure out what value is selected in the slicer - below this is done using the _NumWeeks variable, because we want the visual to update when the user narrows the date range. In your example, you would replace _NumWeeks with _SelectedAccountType2 = SELECTEDVALUE( TableName[AccountType2] ) 

Then you'd need to adjust the other variables accordingly - let me know what your pbix layout and parameters look like if you still need more hints than this. 🙂

Date Field Parameter Selector =
-- apply this measure as a visual level filter using 'TopN' to filter for Top 1 Date Field Parameter
VAR _CurrentDateFieldParameterOrder = MAX( 'Date Field Parameter'[Date Field Parameter Order] )
VAR _NumWeeks = CALCULATE( DISTINCTCOUNT( 'Date'[Week Starting Monday] ) )
VAR _MAXDateFieldtoDisplay =
    SWITCH( TRUE(),
        _NumWeeks > 52, "Year",
        _NumWeeks > 10, "Month Year",
        _NumWeeks > 2, "Week Starting Monday",
        "Calendar Date"
    )
VAR _MAXDateFieldtoDisplayOrder =
    MINX(
        FILTER(ALL('Date Field Parameter'),
        'Date Field Parameter'[Date Field Parameter] = _MAXDateFieldtoDisplay
        ),
        'Date Field Parameter'[Date Field Parameter Order]
    )
RETURN
--_MAXDateFieldtoDisplayOrder