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

    Compact table using a table calculation as the sort

    Michael Nealey

      Hi.

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

      Compact Table Simple Measure Sort.png

      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?)

       

      Compact Table Table Calc Sort.png

      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
          Michael Nealey

          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
            Andrew Ball

            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