Order of rows in unpivoted table

[Anonymous]

I have an unpivoted table used to create a stacked column chart. See below.

The chart does not display the Financial Years in the correct order. I added an Index column but this didn't work because the table has duplicate values (each financial year). Is there a way to get the financial years in the correct order?

Thanks 

Steve

[v-gizhi-msft]

Hi,

You can change it sort by Financial Year through clicking options on this visual:

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thank you all for the suggested solutions. While some approaches did not seem to work initially, when I deleted the chart and rebuilt it they did work, so I am happy!

Steve 

[HotChilli]

This is a sneaky feature of 'Sort by Column'.  You either have to remove the relevant field from the visualisation and re-add it OR delete the visual and start again.

[Ashish_Mathur · Accepted solution]

Hi,

My suggestion would be to split the Financial Year into 2 columns - delete the second column and rename the first column as Year.  Assuming January is first month of your FY, write this calculated column formula

Date=1*("1/1/"&Data[Year])

Format this as a date column.

Now create a Calendar Table and in a calculated column of the Calendar Table, extract the Year.  Build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year from the Calendar Table.

[amitchandak]

Can you create a custom sort column and mark it as sort

[HotChilli]

How about duplicating the column, split it to get the first 4 numbers (call it start year or something).  Set the data type to be number.

You can then use that with the 'Sort by Column' functionality.  i.e. Select Financial Year-> Sort By Column -> start year