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?

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

How to order data on a clustered column chart

5 Replies

ExcelMonke
Impactful Individual
2 years ago
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
mark_endicott
Super User
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    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".
ajohnso2
Solution Supplier
2 years ago
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.