Settings of X Axis in Clustered Column chart

[GD61]

Hi,

The following graph is close to what I need but I have an issue about the X Axis which represents the 12 months of the year(s)

It displays one month out of two: if I display every month (Type = Category instead of Continuous) the months are sorted by decreasing Amount.

If I replace the numbers by the month name (Month_Txt) the months are once again sorted by decreasing Amount.

The sorting of Month_Txt according to Month_Num doesn’t work: the months are still sorted by decreasing Amount.

What I did wrong? My example file is here:

https://www.dropbox.com/s/cd42gf98q42jmkq/Clustered%20Column%20Chart%20Month%20on%20Axis%20Year%20on...

Thanks for your help

[v-sihou-msft · Accepted solution]

Hi GD61,

To make your chart sorted by Month_Num but keep Month_Txt on Axis. Please follow steps below:

1. Insert the Month_Txt into Axis, clear Month_Num. Then you will see your chart sorted by Amount in descreasing order.

2. Now you go the Sory By drop down unselect Amount, see screenshot below:

After that you chart will sorted by Month_Num as you expected.

I also attach the modified .pbix file.

[v-sihou-msft · Accepted solution]

Hi GD61,

To make your chart sorted by Month_Num but keep Month_Txt on Axis. Please follow steps below:

1. Insert the Month_Txt into Axis, clear Month_Num. Then you will see your chart sorted by Amount in descreasing order.

2. Now you go the Sory By drop down unselect Amount, see screenshot below:

After that you chart will sorted by Month_Num as you expected.

I also attach the modified .pbix file.

[GD61]

@v-sihou-msft

Great! That works !

Thanks a lot

[maracles · Accepted solution]

@v-sihou-msft's answer is I think what you are looking for, however I would also recommend that you investigate date tables if you are not already using them. 

There are various links below on different ways of doing it e.g. creating your own, generating it with DAX, downloading one from the data store etc. There are numerous benefits one being that you can create customer date columns which act as labels for your charts eg. (01) - Jan, (02) - Feb. This works particularly well for our business as our fiscal year is not the same as the calendar year. 

Links:

http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...
http://www.powerpivotpro.com/2014/02/the-ultimate-date-tablerevisited/
http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

[GD61]

Hello,

I'll try, Thanks a lot

[mihart]

Hi,

If you change Month_Num to Month-Txt and then Sort by Month-Txt, it works fine. 

click the ellipses in the top right corner, select Sort by and choose Month_Txt.  If you need to change the sort order, click the ellipses again and click the AZ arrow to change it to Jan....Dec.

HTH,

Michele