cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 Established Member
Established 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 Senior Member
Senior Member

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 Established Member
Established 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 Senior Member
Senior Member

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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 2,587 guests
Please welcome our newest community members: