cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stevek Regular Visitor
Regular Visitor

Transpose axis values in chart

I want to create a chart from this data:

Financial Year endingProjects under $20KProjects $21K - $50kProjects $51k - $100kProjects over $100kTotal
30/06/20105351831
30/06/201111891442
30/06/2012111251644
30/06/201388112855
30/06/2014381820
30/06/201512429
30/06/201600167
30/06/201700156
30/06/2018041510
30/06/201901124

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Transpose axis values in chart

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 4
d_gosbell Senior Member
Senior Member

Re: Transpose axis values in chart

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 endingCategoriesProject Count
30/06/2010Projects under $20K5
30/06/2010Projects $21K - $50k3
30/06/2010Projects $51k - $100k5
30/06/2010Projects over $100k18
30/06/2011Projects under $20K11
30/06/2011Projects $21K - $50k8
30/06/2011Projects $51k - $100k9
30/06/2011Projects over $100k14
stevek Regular Visitor
Regular Visitor

Re: Transpose axis values in chart

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.

 

Capture7.PNG

Is there a way to show the data in the correct order, with the correct category labels?

 

Thanks

Steve

 

d_gosbell Senior Member
Senior Member

Re: Transpose axis values in chart

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.

stevek Regular Visitor
Regular Visitor

Re: Transpose axis values in chart

Thank you. I was also able to apply the same approach to the conditional column that I had created initially. I did not work until I used the Sort by Column command.

 

Steve