Forum Discussion
Power BI Clustered Column Chart Legend Behaviour
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
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 Owen,
I've found the issue. It was me using the clustered chart instead of stacked chart. I've also applied @krishnakanth240 's suggestions and it worked! Thanks alot.