Help for stacked column charts of two categories being repeated for multiple times

[Adumio]

Hello everyone,

I have the sample data table below.

Group	Type	Y Value
Group 1	Type A	1
Group 1	Type B	2
Group 1	Type A	2
Group 1	Type B	2
Group 1	Type B	3
Group 2	Type A	1
Group 2	Type A	2
Group 2	Type B	2
Group 2	Type A	1
Group 2	Type B	1
Group 2	Type B	2
Group 2	Type A	2
Group 3	Type B	3
Group 3	Type A	3
Group 3	Type A	3
Group 3	Type A	1
Group 3	Type B	1
Group 3	Type A	1

What I want to achieve with Power BI is the following chart. The stacked columns are added by sequece.

It would be great if all charts can be combine in one single visual, so I can use slider to select what group to show. 

If not, one chart in one visual is still good.

Would you please suggest me how to do it in Power BI?

Note: The actual data has several hundred groups, two types, ~ 100 rows/group. Normally I will just need to select and make a comparison of 2 - 5 groups.

Thank you.

[MNedix]

Hi @Adumio ,

I'm not going to ask you why you want to do this - I am sure you have your reasons. I may have some ideas which could be put in practice in this case but I'm not 100% sure they will answer your question. I don't know how your data actually looks like and what the Types represent but your "problem" is that Power BI doesn't understand that you want to have different entries in the bar (on top of each other). So I was thinking of two ways of solving this:

Option 1. Re-index:

a. Re-index the entries and make them independent. Let's say you add an index column and then build a "Fake Type" based on the Type and Index (use CONCATENATE or CONCATENATEX for this). This will give you unique Type entries (see below).

b. "extract" the color base on the type (e.g. Type A -> Color A, Type B -> Color B)

c. Create a new table with the desired colors and link it to the main Table

d. Trick the chart formatting to use the color table for the Types (skipping the Fake Type stage). I know it's possible, I just don't know it off the top of my head.

Option 2: Use Dates (not sure if this would work though, this may create other problems in itself)

a. if you have dates associated with your entries then use them for the Y axes to do the stacking cadence

If this solved your problem then please mark it as the solution so others can see it

Thanks,

[Adumio]

Hi @MNedix,

Thank you for your suggestion. Creating a "Fake Type" would be a good move. I see that to plot them in the correct order, we need to use numbers 01, 02, ..., 09 instead of 1, 2, ..., 9. 

I have changed the types "A" and "B" to types "Gray" and "Orange" which will be the colors of the bars too.

Group	Type	Y Value	Index	Fake Type
Group 1	Gray	1	01	01_Gray
Group 1	Orange	2	02	02_Orange
Group 1	Gray	2	03	03_Gray
Group 1	Orange	2	04	04_Orange
Group 1	Orange	3	05	05_Orange
Group 2	Gray	1	06	06_Gray
Group 2	Gray	2	07	07_Gray
Group 2	Orange	2	08	08_Orange
Group 2	Gray	1	09	09_Gray
Group 2	Orange	1	10	10_Orange
Group 2	Orange	2	11	11_Orange
Group 2	Gray	2	12	12_Gray
Group 3	Orange	3	13	13_Orange
Group 3	Gray	3	14	14_Gray
Group 3	Gray	3	15	15_Gray
Group 3	Gray	1	16	16_Gray
Group 3	Orange	1	17	17_Orange
Group 3	Gray	1	18	18_Gray

I have searched around, it seems Power BI doesn't have the feature of differentiating the stacked bars' colors based on other column (column "Type" in my case). 

So I decided to change the color of each bar manually to achieve what I need.

Hopefully, someone can advise me a solution (smarter and faster way to do it).

Thank you.

[Adumio]

I have tried to work with my actual business data, just two groups, two color types, and ~ 200 rows of Y values (equivalent to ~ 200 "Fake Type"). Power BI only allowed to show the visual of the first 60 categories in the stacked bars (so I missed ~ 140 rows of data), and it's crazy to change the colors of 60 - 200 categories/ Fake Types (!). 

[MNedix]

It seems that there is a thread opened for the ability to conditionally formatting the Legend of a chart (since the Fake_Type data in Legend - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=31fd95a4-308f-4a28-a3d4-10489b973642).
However, perhaps PowerBI is not the right tool for this. From what I understand, you want to stack 200+ values on top of each other in one column or bar. The only case I know where this applies (and this is based on my professinal experience) is gene cluster analysis in spatial biology - see Seurat analysis (screenshot - each "line" represents the distribution of a certain gene (on the left) in a cluster of cells (on the top)).

I don't know if this solved your problem, it would be great if it did or if gave you another resolution path.

[Dinesh_Suranga]

Hi @Adumio ,
Are you happy with following ?

Or do you want to stack in same order in table?

Thanks.

[Adumio]

Hi @Dinesh_Suranga,

Thank you for spending your time on this. I would want to stack in same order in the table.

It should look like this.

Thank you.