Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Pivot table - is there a way to display the calculated column in a particular order?


Use Case:

Regions:  CAN, US, MEX


DESIRED OUTPUT: Want the columns to display as US, CAN, MEX

But the Pivot table auto sorts it  to CAN, MEX, US

I did try the "Preserve the original sorting order" under the options tab of the Pivot tab and that didn't work either


Thank you!


1 answer

1 accepted

2 votes
Answer accepted

Hi @Dolly Kirubavathi ,

The Pivot Table macro sorts your items alphabetically, but it seems that your need your own custom sorting. So, here is a workaround - I'm not sure if you have Server or Cloud, so for the first option just nest your macros directly and for the second option use the Table Toolbox macro (other steps are the same).

Wrap your Pivot Table macro into the Table Transformer and then into the Table Filter macro:

Mon 2-1.png

Use the Table Transformer macro to assign additional numbers to your items:

Mon 2-2.png

T1.'Region' LIKE "US"
THEN "1"
T1.'Region' LIKE "Can"
THEN "2"
ELSE "3"
AS 'Status'

And then use the Table Filter to hide this additional 'Status' column if necessary:

Mon 2-3.png

Here we've checked the Hide Columns option in the Filters tab and chosen which column to hide in the Filtration tab -> Set filters' default values menu:

Mon 2-4.png

Hope this helps your case.

Thank you!

The solution you have suggested is exactly how I had manipulated the sorting order up till using the Table transformer. I then use the Pivot table to read this table transformer data and display it. I have not used a table filter. 

Please advise if I have to use the Table Filter to achieve my goal here?

Here we used the Table Filter macro only to hide the additional column with numbers used for sorting.

The standard sorting of the Table Filter macro allows you to sort into ascending or descending order - I see that it is not the case, cause you want to display US, Canada and Mexico (not the standard C-M-U or U-M-C sortings).

I tried the Table filter route as well. In the table filter preview it shows the correct soring but when I wrap it with the Pivot table my sorting goes away?

The Pivot Table macro sorts data alphabetically by default.

So, I suggest using the Table Transformer on the top of the Pivot Table macro to assign a custom sorting order.

But the output desired is the Pivot table format, for the markets to be displayed as Columns with Vehicle info as Rows and the number of Vehicles in that market as "Calculated Column".

If I wrap the table transformer on top of the Pivot will I still get the same output?


I currently have this:  table transformer with my desired output wrapped in a pivot table

Yes, of course - the Table Transformer macro sees output of the Pivot Table macro as a regular table, it won't ruin anything. 

I mean that you can create different combinations with our macros - they work with the previous level only.

For example, you have a big manually created table with, let's say, 100 rows. You wrap it into the Table Filter macro to leave only required countries - the output 1 is a table with 70 rows. Then you wrap the Table Filter macro into the Pivot Table macro and aggreate your data - the Pivot Table will see only 70-row table, not the original 100-row table. The output 2 will be a 3 row table as for your case.

Then you use the Table Transformer macro - it works with this 3 row table, adds numbers to your rows and sort them - it's the output 3.

The output 4 will be to use another Table Filter and hide the additional column with numbers created on the previous step.

By the way, one of a wide spread use cases for the Table Transformer on top of the Pivot table macro is to rename the automatically generated columns with long names such as "Sum of something".


And what for do you use your first Table Transformer? That is wrapped in the Pivot Table macro?



This is my format. Will I be able to preserve this format by wrapping it in Table transformer?

With such multi-dimensional pivot the format will be ruined - the Table Transformer may combine the merged cells in the headers and you'll get smth like 'Literal of OTA Markets US'.

And you'll need another query to rename your colums and define their sequence - my previous answer covers the rows sorting.

Can you please wrap the Pivot Table into the Table Transformer macro without any SQL queries and make me a screenshot of your modified header row? I'll recreate the table and provide you with a new SQL query.



The above is after I took the Pivot table and wrapped it into a Table Transformer and renamed the columns. So, thank you! this works...

But the bigger concern here is ,

1. When new markets get added, this Wiki page has to be manually updated for those new Markets. In my previous design, with only the Pivot table output no additional manual intervention is needed once you publish the wiki no matter how many markets get added in the future.

2. What does "preserve the original formatting" in the Pivot table really do? I thought that was to honor the sorting i had with table transformer that is the source to this Pivot table.


thanks hugely for all your help!!!!!

You mean that the "Preserve default sorting" option for the Pivot Table doesn't work for this case? It ssems that's because your table is a complex one, we have a remark that "Default sorting is available only in the pivot tables with one column selected as row labels".

What about my question #1 ? Do you agree with my thoughts there? any ideas from your side to get out of this manual intervention?


Also, the output I attached is what you were suggesting correct? when you said you will give me a SQL query? Just validating that I got this solution correct, that's all...

I've thought that you did everything right as you attached a screenshot with the shortly renamed colums.)

Here is a similar question where it is shown that you can list your columns in a certain order via the Table Transformer and they will be shown in that precise order.

What concerns the new markets and manual correction - nothing more from my side, unfortunately.

Thank you for all your help!!!!!! 

Suggest an answer

Log in or Sign up to answer

Atlassian Community Events