cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

custom sort columns on a stacked chart

 

I have a stacked column chart with counts of statuses.  Currently I can only have the chart sort alphabetically by Status Name.  For example, Initiated, Ready for Review, Pending, Interim, Final, Closed.  I would like the Status to sort chronologically by when they happen in my process not by the alphabet.  So I would would want them sorted as above. 

 

I remember this being covered quickly at the conference in October but didn't take good enough notes.  I remember creating a new table from my data table, "Include in Refresh Report" being set as well. Then joining the new sorting table with the original data table.  Adding a new column with numerics to control the sort order.

 

Is there an article out there?  Or can someone help me fill in the details that I am missing.  I am using Power BI Desktop to host my reports internally.

 

Thanks in advance,  Scott

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: custom sort columns on a stacked chart

@wskallmeyer

 

Please follow these steps.

 

 

The table with status you can create in Excel or another and add to the model.




Lima - Peru

View solution in original post

Highlighted
Microsoft
Microsoft

Re: custom sort columns on a stacked chart

Absolutely.  You just need to create another table that will be used just for sorting.

 

The new table needs 2 columns.  One column containing the exact values that match your statuses.  The other column should be numeric and will control the sort order.

 

Join the column containing the statuses back to your original table.  Set it to sort by the other column.

 

The main thing is you need to use the Status column from the new table on your axis - you can't continue to use the status column from your original table.

 

Does that make sense?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: custom sort columns on a stacked chart

@wskallmeyer

 

Please follow these steps.

 

 

The table with status you can create in Excel or another and add to the model.




Lima - Peru

View solution in original post

Highlighted
Microsoft
Microsoft

Re: custom sort columns on a stacked chart

Absolutely.  You just need to create another table that will be used just for sorting.

 

The new table needs 2 columns.  One column containing the exact values that match your statuses.  The other column should be numeric and will control the sort order.

 

Join the column containing the statuses back to your original table.  Set it to sort by the other column.

 

The main thing is you need to use the Status column from the new table on your axis - you can't continue to use the status column from your original table.

 

Does that make sense?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Advocate II
Advocate II

Re: custom sort columns on a stacked chart

I went to "Enter Data"  Added my 2 columns with Status (name matching) and a SortOrder column into a new table.  Saved it.  Went to Modeling and linked the source table to the sort table using the common column Status.  Also, set the SortBy column to SortOrder which has a type as Whole Number.

 

Went to add a new visual Line and Stack Chart.  To the "column values" I added Status from source table as Count of Status.  To the "Shared axis" I added Status from the sort table.  It will still only sort alphabetically.

 

Still not sure what I am doing wrong. 

 

 Scott

Highlighted
Advocate II
Advocate II

Re: custom sort columns on a stacked chart

Figured it out.  I needed to highlight the column then go to the ribbon and "Sort by Column" to my new SortOrder column.

 

Thanks for everyone's help.

 

Scott

Highlighted
Frequent Visitor

Re: custom sort columns on a stacked chart

Hi Phil,

Thank you for the solution.

But I do not see that it works with other vidgets. For example, if I want to create stacked bar chart, which shows in dynamics how my value is changing in terms of  structure (structure = category which I put into the color coding), the function 'Sort by column' is unavailable. 

Is there any solution for that vidget? Thank you in advance.

Highlighted
Frequent Visitor

Re: custom sort columns on a stacked chart

Hi Phil,

Thank you for the solution.

But I do not see that it works with other vidgets. For example, if I want to create stacked bar chart, which shows in dynamics how my value is changing in terms of  structure (structure = category which I put into the color coding), the function 'Sort by column' is unavailable. 

Is there any solution for that vidget? Thank you in advance.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors