cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
oldhasbeen Member
Member

Issues sorting Stacked Column Visualsation by Month

Hi

 

I've got a simple visualisationof some Sales vs Target data:

 

SalesData.PNGSAlesGRaph.PNG

Only problem is the column sort order - I want it by month, but for some reason PBI has sorted it in descending revenue order.

 

I've tried to correct this by selecting the "Month" column and using Modelling/Sort by, but there is no "Month Number" option offered:

WhatIget.PNG

Could anyone explain this and offer a solution?

 

Thanks!!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
JosefPrakljacic Senior Member
Senior Member

Re: Issues sorting Stacked Column Visualsation by Month

Hey @oldhasbeen,

 

in a nutshell. PowerBI does not know that the column "Month" is a Month/Date column. For PowerBI it is a text column containing string values.

 

What options do you have now?
Best Practice -> You create a Date Dimension and work with it. (Link to an article about it)

 

-OR-

 

  1. You create a calculated column that represents the number of months. That means -> Jan = 01, Feb = 02 and so on.
  2. This column you combine with the year column so that the new column looks like this -> 201801, 201802 ....
  3. On the basis of this column you can then sort your [Month] column.

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

View solution in original post

venal New Contributor
New Contributor

Re: Issues sorting Stacked Column Visualsation by Month

@oldhasbeen 

 

Please go through the below steps.

MonthOrder-1.JPGMonthOrder-2.JPG

 

Select the "Table" in left side pane "Queries", click on "Merge Queries" and selece the common column from both the tables(Table,Order). Once it's loaded, expand the column "Order". So that you will get few other columns, you can keep order column and remove other columns.(Order.Month) and rename Order.order as Order.

 

MonthOrder-3.JPG

 

Once it's done, please click on "Close and Apply".

 

Home >> Data View >.> select the column (Month) >> Click on "Sort by Column drop down" >> select "Order".

 

MonthOrder-4.JPG

 

Remove the existing Bar Chart and create a new one.

 

MonthOrder-5(Final Output).JPG

 

Hope it will helpful to resolve your issue.

 

If this post was helpful may I ask you to mark it as solution and give it some kudos?

 

Best Regards,

Venal.

View solution in original post

4 REPLIES 4
JosefPrakljacic Senior Member
Senior Member

Re: Issues sorting Stacked Column Visualsation by Month

Hey @oldhasbeen,

 

in a nutshell. PowerBI does not know that the column "Month" is a Month/Date column. For PowerBI it is a text column containing string values.

 

What options do you have now?
Best Practice -> You create a Date Dimension and work with it. (Link to an article about it)

 

-OR-

 

  1. You create a calculated column that represents the number of months. That means -> Jan = 01, Feb = 02 and so on.
  2. This column you combine with the year column so that the new column looks like this -> 201801, 201802 ....
  3. On the basis of this column you can then sort your [Month] column.

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

View solution in original post

venal New Contributor
New Contributor

Re: Issues sorting Stacked Column Visualsation by Month

@oldhasbeen 

 

Please go through the below steps.

MonthOrder-1.JPGMonthOrder-2.JPG

 

Select the "Table" in left side pane "Queries", click on "Merge Queries" and selece the common column from both the tables(Table,Order). Once it's loaded, expand the column "Order". So that you will get few other columns, you can keep order column and remove other columns.(Order.Month) and rename Order.order as Order.

 

MonthOrder-3.JPG

 

Once it's done, please click on "Close and Apply".

 

Home >> Data View >.> select the column (Month) >> Click on "Sort by Column drop down" >> select "Order".

 

MonthOrder-4.JPG

 

Remove the existing Bar Chart and create a new one.

 

MonthOrder-5(Final Output).JPG

 

Hope it will helpful to resolve your issue.

 

If this post was helpful may I ask you to mark it as solution and give it some kudos?

 

Best Regards,

Venal.

View solution in original post

oldhasbeen Member
Member

Re: Issues sorting Stacked Column Visualsation by Month

I think I've done as you suggested but I keep getting the wrong results. I've tried using a table wuith month name but that doesn't work either. Here's what UI get:

 

Table is sorted by alpha month name, graph by revenue. WTF.PNG

 

See https://1drv.ms/u/s!AmxJyApgEAcYgv0OGkLkcYXpbAHnDw?e=0EaKh1

oldhasbeen Member
Member

Re: Issues sorting Stacked Column Visualsation by Month

All working now!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors