Transpose Table (Display measures as Rows)


(sam) #1

The Goal

Say you want to transpose your table; that is, show the measures as rows. That is, if your data looks like this:

The goal is to show all the different Count measures grouped together, rather than all the Years grouped together. This is possible by using dimension fill to manufacture some rows, and then using a table calc to fill in those manufactured rows.

The finished product will look like this:

The Process

Step 1: Case Dimension

Make a dummy case dimension with as many cases as there are measures. In this example:

  dimension: dummy_three {
    case: {
      when: {
        label: "Count"
        sql: 1=1 ;;
      }
      when: {
        label: "Count Inventory Items"
        sql: 1=1 ;;
      }
      when: {
        label: "Count Orders"
        sql: 1=1 ;;
      }
    }

Step 2: Pivot and Dimension fill

Pivot on your original dimension, include the dummy dimension, and dimension fill the dummy. Here we’re taking advantage of the fact that case statements can be filled even if they don’t have data. This step will look like this - all the data will be in the first row for now.

Step 3: Make Table Calculation

Now we need to make a table calc that gets the data out of that first row and into the second and third rows. The calculation here is checking the row number and inserting the proper measure value into that row.

coalesce(
    if(row()=1, ${order_items.count}, null),
   if(row()=2, offset(${order_items.count_orders}, 1-row()), null),
   if(row()=3, offset(${order_items.count_inventory_items}, 1-row()), null),
    null)

Step 4: Touch up Visualization

Finally, hide the measures. The table calculation should have the only values you need.

That’s it! Feel free to leave any questions or iterations on this method in the comments below.


Multiple measures over different historical time periods
(Alex Hancock) #2

Good hack - would be good to have a measure group measure that could be added to the end of each row so that analysts could do this on demand though :slight_smile:


#3

Thanks, thats a great hack!

But talking about this as an out of the box feature - I think that transposing table as a simple button click would be a very nice feature for business users.


(Dave King) #4

Appreciate the hack, but when is Transpose Table going to be a proper feature? It’s been talked about on Discourse for 2+ years!


(romain.ducarrouge) #5

Hey @daking13
This type of visualization is not currently available in Looker and I do not have a timeline for it. However, I am letting our product team know that you’d like to have this in Looker!
In the meantime, we support custom visualizations in Looker so if you want you can make your own viz with JS which will transpose the table.


(Greg Roberts) #6

+1 for this.

In almost all cases, you’ll end up with data which is not “Tidy”; but many stakeholders do not care about this distinction!