Creating a stacked Column chart wiht multiple columns

[stjimi]

Hi all, 

I can't seem to work this out. I want to create a stacked column graph where each column in the graph relates to a column in a table.  I want the values in each column to be represented in the stack by percentages.  

For example, I want the Criterion 5 to have 70 percent of the column = '0', 20 percent ='1' , and 10 percent =  '2.' 

Criterion 1	Criterion 2	Criterion 3	Criterion 4	Criterion 5
1	0	2	0	1
2	0	2	0	0
2	1	1	2	0
1	1	0	1	1
0	2	2	3	0
3	3	1	0	2
0	1	0	0	0
1	0	2	2	0
1	2	0	0	0
1	1	1	1	0

I have made it work for one criterion but can't get more than one column. 

Any ideas? 

Cheers! 

[v-yetao1-msft]

Hi @stjimi 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @stjimi 

You need to find a way to combine the column names of your column into one column, otherwise multiple columns will be displayed when added to the stacked Column chart, instead of being displayed in one column.

If possible , could you provide your pbix file ? Because I don’t know how your calculated columns are obtained, so I can’t find a way to deal with them .

Best Regard

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @stjimi 

(1)In Power Query , use Unpivot columns to convert the column name to row value , like the screenshot below . To prevent the same row from being hidden, an additional index column needs to be created .

(2)Create a measure to count the number group by column [Attribute] and [Value] .

Count = CALCULATE(COUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute],'Table'[Value]))

(3)Add a 100% stacked column chart , put column [Attribute] in Axis ,  [Value] in Legend , [Count] in Values .

The result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

[stjimi]

Thanks so much Ailsa 
The trouble is that these are calculated columns and so won't let me alter them in pPower Query. Is there a work around?