Forum Discussion
Power BI Clustered Column Chart Legend Behaviour
Hi, I am a little bit confused on why my clustered column chart displayes data with so much gaps in between after adding in a legend.
I have a clustered column chart to displays the total profit margin of each sales person in each region. I want to add a legend to visualise the different region for each sales person. Or even better, make the indivual sales person column in each region 'stick' closer together.
This is before adding the legend.
This is after adding in the legend.
Hi Brandon_Soon,
Workaround is to create a combined field i.e., Region - Sales Person. X-axis: Region - Sales Person, Y-axis: Profit Margin, Legend: Region
Thanks for testing that!
Unfortunately there seems to be no way around that behaviour when using a Small multiples field.
The values displayed on all X-axes are identical. The axis values included are those for which the measure is nonblank in at least one small multiple. The normal blank-hiding rules don't appear to apply in this case 😞
Alternatively, you may want to consider a custom visual like Deneb where things can be customized and the x-axes made independent. See attached PBIX where you can examine the Vega-lite specification I used on the Deneb page.
There is more effort involved to format a Deneb visual set up in this way, however.
You may also want to consider krishnakanth240 's suggestion.
Note: I used Stacked column rather than Clustered column in my earlier examples.
Regards
8 Replies
Hi Brandon_Soon
Here are some suggestions in attached PBIX (using a different dataset but similar structure):
1. Use Region as a legend field and a copy of Region as a Small multiples field. The copy is needed since the same field cannot be used in both Legend and Small multiples.
Note: The space between small multiples and the space between bars can be set independently.
2. Use Region as a Small multiples field, but, rather than using a Legend field, create a measure that returns the desired colour (based on the value of Region), and apply this measure under Format > Columns> Color.
3. Don't use Small multiples at all but use a Region and Sales Person on the X-axis and the same conditional formatting measure as option 2.
Would any of these options work for you?
Thanks Owen,
The first option worked for me, however I realised the using the small multiple field, it duplicates my sales person for each region. Each of my sales person data is connected to its respective region like this:
So after i updated the small multiple fields it looked like this:Leaving some empty spaces for sales person who do not belong in that region. Are there any workaround for this?
Thanks for testing that!
Unfortunately there seems to be no way around that behaviour when using a Small multiples field.
The values displayed on all X-axes are identical. The axis values included are those for which the measure is nonblank in at least one small multiple. The normal blank-hiding rules don't appear to apply in this case 😞
Alternatively, you may want to consider a custom visual like Deneb where things can be customized and the x-axes made independent. See attached PBIX where you can examine the Vega-lite specification I used on the Deneb page.
There is more effort involved to format a Deneb visual set up in this way, however.
You may also want to consider krishnakanth240 's suggestion.
Note: I used Stacked column rather than Clustered column in my earlier examples.
Regards
Hi Brandon_Soon,
Workaround is to create a combined field i.e., Region - Sales Person. X-axis: Region - Sales Person, Y-axis: Profit Margin, Legend: Region
Hi Krishnakanth
That was what I've done for my first screenshot. The results made the bar skinny , I was just wondering if there is a way to make the width of the bar wider after adding on the legend field.Hi krishnakanth240
I've changed my chart to a stack chart instead of a clustered chart and it works now! Thanks alot for helping!
