Stacked Bar Graphs - Number of available Color removes a big chunk of data - need ideas

[NoeZ]

Hi all,

Context:
I built a business forecast analysis where you can choose the type of analysis using a parameter (Customer / Product Family / Sold-to Region / Type of demand / etc)

Example Below.

The issue I'm facing is the following:

Since I'm leaving the user to choose the data he wants to select as "Legend" in the Stacked Bar Graph (and matrix), if he choses the "Product Reference" or "Customer name" on a big scope, we go over the maximum amount of colors, and the bar graph simply ommits a huge amount of the business

(can go up to 60% of business missing since the colors are allocated alphabetically and we have huge customers starting with S->Z)

My first idea:
Was to create a new column with a reworked sold-to name, according to certain criteria, the first (say... 160?) biggest customers would show up, the rest would be classified as a single "OTHER" Customer (Same with the product)
--> The big issue with this is that this data pulls from the past 7 years and goes forward another 4 years, and another parameter allows the user to see either quantities sold or revenue ($)  so depending on the data the  user choses, the criteria I chose to have might not be coherent, and the user could have a sizable amount of "OTHER" on the screen.

Is there any other way to circumvent this issue caused by the color palette size?

Open to ideas 🙂

Thanks

NoeZ

[lbendlin]

What value does the color palette provide? Have you considered using "Top 10 and Others"  patterns?

[NoeZ]

Hi Ibendlin,

On the bar graphs the color gives the advantage of seeing instantly the split, for example when looking at the upcoming 12 months we can instantly see what is Backlog (Firm sales order), what is forecast, and what is Sales Contracts.

Can I activate a function to reduce the number of colors to Top 10 and others ? 

I've never used this, do you have a link to a tutorial by chance?

[lbendlin · Accepted solution]

Showing the top 5 products and Other row - SQLBI