(Confirmed not possible)Stacked Bar Single Colour for All Legend Categories avoid "Too many values"

[Isambard]

Hi. I don't know if this is possible but I really hope it is. I have data in the format:

Item	Team	Score
Item1	Team1	5
Item2	Team1	10
Item3	Team2	10
Item4	Team2	20

I have plotted this into a stacked bar showing the sum of score by team. There are about 10 teams so this looks fine. There are several hundred unique items and the total summed scores are between 100-500.

In addition to seeing the total for each team I would really love to give a rough sense of the number of items within each team's column, ie if the total is composed of many low scoring items or a few high scoring items. Assigning [Item] as the legend seemed like a good way to do this. I then intended to hide the legend box (I don't need to see which item is in which column, just how big they are) and set all the colours the same, with a border so the visual would make clear roughly how many item blocks made up each column.

However I have two problems. Firstly when I assign the legend I get an error telling me there are two many values - most get hidden in the visual which makes the sum totals wrong. I understand this functionality is to stop visuals having hundreds of legend items which would be iligible, but in my case I don't need to see the specific breakdown, just the gist.

Secondly there doesn't seem to be an obvious way to change the colours for every [Item] in the legend to the same thing. The Format Visual>Columns>Colour menu has an "All" setting, but this doesn't apply changes anywhere. It will be really impractical to manually format hundreds of identical categories.

Thanks for any help. If what I'm trying to do is impossible that would also be great to know. Thanks

[tayloramy · Accepted solution]

Hi @Isambard, 
 

What you want (using Item as the legend so each column is subdivided into hundreds of tiny same-colored segments) isn’t feasible in a native stacked bar. Power BI caps how many legend series a visual will render and shows the warning “Too many values” while silently dropping the rest-so totals become wrong. That’s expected behavior, not a bug. (example thread) There also isn’t a one-click way to set the same color across hundreds of legend categories. You can use themes and conditional color rules, but the series limit still applies (themes, color tips).

Instead, use one of these patterns to convey “many small items vs a few big items” without blowing the legend:

1. Combo chart overlay (recommended):
   • Columns: Sum of Score by Team.
   • Line: Item Count and/or Average Score per Item by Team.
   • This gives readers an immediate sense of fragmentation vs concentration. (combo chart docs)

   Total Score = SUM('Table'[Score]) 
   Item Count = DISTINCTCOUNT('Table'[Item]) 
   Avg Score = DIVIDE([Total Score],[Item Count])
   
   VAR tot = [Total Score]
   RETURN
   SUMX(
   VALUES('Table'[Item]),
   VAR s = SUM('Table'[Score])
   VAR share = DIVIDE(s, tot)
   RETURN share * share
   )

   Put Total Score on the column axis and Item Count or Avg Score as the line.

2. Stack by a small number of bins instead of Item:

   Create 5–7 bins for Score (e.g., 0–5, 6–10, …), then use those bins as the legend. You’ll see composition without hundreds of series, and the totals stay correct. Create bins via “New group” on the field (grouping & binning).

   Score Band = 
       VAR s = 'Table'[Score] 
           RETURN 
               SWITCH( 
                   TRUE(), 
                   s <= 5, "0–5", 
                   s <= 10, "6–10", 
                   s <= 15, "11–15", 
                   s <= 20, "16–20", 
                   "21+" 
              ) 

   Legend = Score Band; Value = Sum of Score or Count of Item.

3. Report page tooltip for “what makes up this team”:

   Keep your main chart simple (Sum of Score by Team). Add a tooltip page that shows a treemap or bar list of Items for the hovered team (all one color if you want). This avoids the series limit in the main visual but still reveals the breakdown on hover. (tooltips)

   Steps: create a Tooltip page, add a treemap with Item as Category and Score as Size, set Page type = Tooltip, then assign that page to the main visual’s Tooltip.

4. Small multiples with a distribution view:

   Build a histogram of Score (using bins) and drop Team into Small multiples to show each team’s distribution at a glance. (small multiples)

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Isambard]

Thanks. Its useful to know that it isn't possible. 

Frustrating that PowerBI forces you to a limited number of legend categories when this isn't a computational limit but a stylistic choice. It would be great if you could dismiss the error box and choose to show the whole data. It would also be a great future feature if Microsoft would impvoe the colour selection options. Even selecting a few colours in PBI is laborious.