Forum Discussion
Transpose axis values in chart
I want to create a chart from this data:
| Financial Year ending | Projects under $20K | Projects $21K - $50k | Projects $51k - $100k | Projects over $100k | Total |
| 30/06/2010 | 5 | 3 | 5 | 18 | 31 |
| 30/06/2011 | 11 | 8 | 9 | 14 | 42 |
| 30/06/2012 | 11 | 12 | 5 | 16 | 44 |
| 30/06/2013 | 8 | 8 | 11 | 28 | 55 |
| 30/06/2014 | 3 | 8 | 1 | 8 | 20 |
| 30/06/2015 | 1 | 2 | 4 | 2 | 9 |
| 30/06/2016 | 0 | 0 | 1 | 6 | 7 |
| 30/06/2017 | 0 | 0 | 1 | 5 | 6 |
| 30/06/2018 | 0 | 4 | 1 | 5 | 10 |
| 30/06/2019 | 0 | 1 | 1 | 2 | 4 |
that has the categories (Projects under $20k, etc) on the X-axis and the number on the y-axis, sorted by financial year. I can show the chart with the financial year on the X-axis, but not the categories.
Any help appreciated.
Steve
Column values are always sorted alphabetically by default. You can override this by setting a sort by column in the table designer. You could create a calculated column wth a switch statement to create a numeric ordering
CategoryOrder =SWITCH( [Category]
, "Projects under $20K", 1
, Projects $21K - $50k, 2
etc...
)
Then click on the [Category] column in the field list, switch to the "Modeling" tab on the ribbon and click on the "Sort by Column" button and choose the new [CategoryOrder] column.
I also typically hide any ordering columns like this one as they are only needed for overriding the default alphabetical sorting.
4 Replies
The easiest way to do this would be to unpivot this data so that it's structured as follows (excluding the Total column). Then you should easily be able to put Categories on X and Year on Y
Financial Year ending Categories Project Count 30/06/2010 Projects under $20K 5 30/06/2010 Projects $21K - $50k 3 30/06/2010 Projects $51k - $100k 5 30/06/2010 Projects over $100k 18 30/06/2011 Projects under $20K 11 30/06/2011 Projects $21K - $50k 8 30/06/2011 Projects $51k - $100k 9 30/06/2011 Projects over $100k 14 Thank you. Your solution works, except for one thing.
When applied to the chart it shows the categories in a different order than shown in the table. It appears an alphabetical order is applied to the data. I added a conditional column to try to change the order. This works only if the next level of the heirarchy is selected and it shows the heirarchical number on the x-axis. See attached.
Is there a way to show the data in the correct order, with the correct category labels?
Thanks
Steve
Column values are always sorted alphabetically by default. You can override this by setting a sort by column in the table designer. You could create a calculated column wth a switch statement to create a numeric ordering
CategoryOrder =SWITCH( [Category]
, "Projects under $20K", 1
, Projects $21K - $50k, 2
etc...
)
Then click on the [Category] column in the field list, switch to the "Modeling" tab on the ribbon and click on the "Sort by Column" button and choose the new [CategoryOrder] column.
I also typically hide any ordering columns like this one as they are only needed for overriding the default alphabetical sorting.