Issues sorting Stacked Column Visualsation by Month

[Anonymous]

Hi

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

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:

Could anyone explain this and offer a solution?

Thanks!!

[JosefPrakljacic · Accepted solution]

Hey @Anonymous,

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

[venal · Accepted solution]

@Anonymous 

Please go through the below steps.

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.

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".

Remove the existing Bar Chart and create a new one.

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.

[Anonymous]

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.

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

[JosefPrakljacic · Accepted solution]

Hey @Anonymous,

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

[Anonymous]

All working now!