Stacked Bar Chart Change default display order of Bars

[dd88]

Status field is a column, text format in the table.

On the report this is default Stacked Bar Chart order of the bars.  It appears the order is in alphabetical order of Status value.

The requirement/ request is to display the Status/Bars in a specific order.

Order
Behind Schedule
Yet to Commence
Not Progressing
In Progress
No status

Blank

How can this be done?

TIA

[Anonymous]

Hi @dd88 ,

Based on your problems, here are my answers.

Firstly you can create an Index column in Power Query.

Back to the Table view, select Sort by column and choose Index.

Finally choose Stacked Bar Chart Visualizations, you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RossEdwards]

Sure can!  The easist method is to create a new table using 'Enter Data'.  Put in each of those values and create a 2nd numeric column called "Sort Order".  Close and Apply to your model and in this new table, select the text field and change the "Sort By" column to the 'Sort Order' column.

Next, create a relationship between this new table and your data.  This is now considered a data dimension.

Lastly, in your visual, replace the Axis to use the field from your new table and you'll find it sorted.

[dd88]

Thank you @RossEdwards  thats great, that worked.

The Status column has blank values, and thats ok as it means the user hasnt entered a Status yet.  I would still like (Blank) displayed.  

In the table I entered a row for blank, status order 7. I entered the value (Blank), and entered as a space character for blank. However both did not work and detect blank as a StatusOrder.  it does not know how to action (Blank) therefore adds as the 1st bar in the Stacked bar chart.

result

Any ideas on the best way to resolve this so (Blank) is detected and displays as the last  order @ number 7

TIA

[RossEdwards · Accepted solution]

Yes, this is becuse there is a difference between (Blank)/null and empty text.  Power BI won't do joins on a null result so you'll need to replace those nulls with another label, then have that label in your sort order table. 

I'd recommend going to your datatable in Power Query, select the column that holds the status value and using the 'replace values' you see on the home set of options.  You could set it as "No Status' and fold them in, or you could make them a separate label.

[dd88]

good one @RossEdwards 

In Transform data, in the main table Status column, I replaced null with (Blank)

and in the table StatusOrder added (Blank)

The visual Stacked bar chart now displays in the required order.

Many thanks again ..