Bar chart - cluster x-axis and show bars for each cluster

[Anonymous]

Hi,
I am looking for a way to cluster x-axis like below and add to bars to each. Below is the table with the data imported to BI from Excel.

Year	0-100.000	100.000-500.000	500.000-1.000.000	>1.000.000
2020	37,13%	40,0%	33,33%	33,33
2021	47,45%	36,41%	29,27%	20,59

Thanks

Thomas

[Tahreem24 · Accepted solution]

@Anonymous ,

DO the following steps:

Step 1: Go to Transform Query Editor

Step 2: Select all the columns except Year and click on "Unpivot Columns" under Transform tab.

Step 3: Save and close the Query Editor.

Step 4: Take the Cluster column chart from visualzation pane and enter needed values on axis and value property.

Attaching screen shot for your reference:

[Anonymous]

@Tahreem24 
Great job! thx.
One thing I can't get to work is the year. Somehow mine is counting. Any trick for that?
Best
Thomas

[Tahreem24]

@Anonymous  Good to know that my solution is working for you. 

Just try to use SUM instaed of Count like below:

[Anonymous]

Hi @Tahreem24 ,
Thanks for your effort.
I noticed this method is summing "year", which makes the bar chart look wrong.

The sum is 4041 (2020+2021) for below chart. Instead it should return two bars with each value from below table.

Hope it makes sense 🙂

[v-shex-msft]

HI @Anonymous,

Have you tried to move your year field to the legend fields of your chart? It should display and summary the value of your records based on attribute and year groups.
Regards,

Xiaoxin Sheng

[Tahreem24]

@Anonymous ,

Make the year column to Text data type.