How to order data on a clustered column chart

[fredo75]

Hello all,

Could you help me to solve this issue:i have a chart(see below) of data with 3 periods.I used a clustered column chart in PBI, so I have 3 columns on 3 different periods, for example: p6=200, p9=300, and p12=0, but when I display a grouped histogram the columns are displayed like this: P12, P6, P9 even if I filter by ascending or descending sort, it is still not in order, it displays: P9, P6 and P12 or P12,P6 and P9.How can i fix this?

 

[ajohnso2]

Your sort order is evaluating in string not integer so technically it is correctly sorted. You can use ExcelMonke's solution or in your data view you can apply a different column (an integer column) to sort your period column by.

[mark_endicott]

@fredo75 - if you would like to sort them in the order of P6, P9, P12, then you can add an Index column in Power Query, this can be used to sort the column later on. 

First head to Power Query and select "Add Column" then Index >> From 1

After clicking "Close & Apply" you will have this column in your data, and on the table veiw you can use "Sort by column" to sort the Period column by your Index. To do this, click the Period column, then use the option from the toolbar to select Index:

You can then sort by Period ASC in the Chart:

If this works for you, please mark it as the solution.

[fredo75]

Hello,

Hello,

Hello Mark,

Sorry for the late reply, but even when i click Close and Apply, i didn't get the column created with the Index from 1 in order to "Sort the Period column by  Index.I have only 2 choices.

Could you tell me how to sort out this issue?

[mark_endicott]

@fredo75 - did you follow my steps to produce the Index column? 

If it has not appeared when you have clicked Close and Apply, then you will need to check your Power Query steps, if you have a remove columns step, this could be using "Table.SelectColumns" - Which you can find out by viewing the M Code in the advanced editor. If this is the case you will need to add the index column into the selected columns. You can do this by clicking the "cog" on the Removed Columns step in the "Applied Steps". 

[ExcelMonke · Accepted solution]

Hello,

You could consider creating a calculated column where you assign the order value to the period in the order you would like. So for example you can consider the following formula:

CalcColumn =
SWITCH(
Table[Period],
"p12", 1,
"p9",2,
"p6,3,
0
)

Then under your column tools, you can sort your period column by the order column