2 Replies Latest reply on Mar 12, 2013 2:16 PM by Andrew Ball

# Compact table using a table calculation as the sort

Hi.

I'd like to build something akin to a compact table that "The Information Lab" once built - this:

The sort here is on a simple measure, in this case, sum of sales. Fine, no problem.

My real world problem, however, has a table calculation (don't they all?)

The East measure I have highlighted is the same dimension (A1) as the measure highlighted for Central. It is on a different row, of course, because I cannot create this compact table with a table calc. The measures are sorted by the table calcs at left. The bottommost score is the Central Dimension A1 - and my compact table, in an ideal world, would be sorted by the bottom-top dimensions for Central.

If a sample workbook is a better approach, I'll try and draw one up with similar calcs to what my real world has.

• ###### 1. Re: Compact table using a table calculation as the sort

OK - I created a packaged workbook that I hope will explain my question better.

As you can see, North Carolina office supplies field has the lowest profit ratio z-score at -6.77237.

The North Carolina technology field is blank, however. Only down towards the bottom of the table do you see this score - -0.79885.

I would like to see both of these values for North Carolina (and all states) on the same row, sorted by the first column, or whatever column I choose. I'm fairly certain I need to use the index function (Rank)...but cannot figure out how to do this.

• ###### 2. Re: Compact table using a table calculation as the sort

Hi Michael,

The reason you are getting the view you are is because of the "Profit ratio z-score" field on the rows. Because this is discrete, the values in the table are on the row that matches. This means (as you saw), that the technology field cannot be on the same line, as it does not have the score of -6.77237 needed to be on that row.

The simplest solution is just to remove the "Profit ratio z-score" field from rows. The view is then what you want, and can be sorted however you wish

Andrew

1 of 1 people found this helpful