Transpose Table (Display measures as Rows)

Starting in Looker 6.14, Transpose Table is a visualization option for the Table Next feature!

Note that this approach may not work with all dialects. Specifically, SQL Server/Azure SQL Data Warehouse cannot include constants in the GROUP BY, and will not work for this method.

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, index(${order_items.count_orders}, 1), null),
   if(row()=3, index(${order_items.count_inventory_items}, 1), 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.

7 Likes

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:

1 Like

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.

3 Likes

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

8 Likes

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.

2 Likes

+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!

Here is information on a Custom Viz to use for transposing Measures from Columns to Rows with a new visualization type.

Custom Viz - Transpose Table: This viz transposes measures - so that measures go from columns to rows.

Webinar on setting up Custom Viz (showing this example):

4 Likes

+1 For adding a transpose button. I’m coming to Looker from Datorama, and it’s making me realize how important this feature is. I can’t use the hack described at the top of this post because it doesn’t work with Azure SQL

+1 for transpose button.

1 Like

There’s a feature request topic here: https://discourse.looker.com/t/be-able-to-display-multiple-measures-as-rows-rather-than-columns/2583 that’s worth voting on! We track votes there quantitatively, but don’t track +1 comments here. Adding a vote + a comment with your context for wanting this and how you’d like it to work would be super useful :slight_smile:

Thanks izzy. Added my vote there

+1 for transpose!

Sam, thanks so much for your Transpose hack! I am making a Table Calculation using the formula you’ve provided, but am being told that “index” is an unknown function. Is “index” outdated? What should be used in place?
I tried using “pivot_index” but it didn’t come out quite right. Thanks!

That’s super weird, Jaime. Could it be coming from somewhere else in your syntax?

Maybe try just creating a simple table calc that’s just index(${funnel_metrics.known_names},1) and see if that works, to try and diagnose.

Good news for this request in version 6.14! Looker 6.14 Release Notes