Custom reports

Looker Studio – How to transpose columns of metrics to rows inside a table chart

March 24, 2021 - by Mehdi Oudjida

The quick tip below describes how to transpose Looker Studio (formerly known as Google Data Studio) table charts composed with rows of specific dimension values and column of metrics TO rows of metrics with columns corresponding of the dimension values.

This way to display metrics is really useful in other data visualisation tools to get a summary of KPIs in one chart. The drill down of value of metrics in several columns is not mandatory but easily achievable.

How to transpose table chart with Looker Studio

Result & ingredients

In the picture above, I take the example of Users, Sessions and Pageviews metrics, drilled down by month for the first quarter of the year, from Google Analytics demo account.

To build the transposed chart, we need the following elements:

  • A Google Sheet data source to list the metric names
  • Blended data to merge real data and metric names into one data set
  • A table chart or a pivot table to display our data
  • A calculated field to affect the right metric to the right metric name

Really simple to build.

1. Google Sheet data source

First, we create a simple Google Sheets file with one tab containing the list of metrics and add it as an embedded data source in your report.

Metric names list to transpose

2. Blended data

Then, configure a blended data as described below for our example:

  • As left table
    • Data source: Main data source, here Google Analytics data source
    • Dimension: Month of Year
    • Metrics: Users, Sessions, Pageviews
  • As right table
    • Data source: Google Sheets data source with metric names
    • Dimension : “Metric name”
  • Join configuration
    • Operator: Cross join
    • Join keys: None
Data blending to transpose the data set

3. Table chart or pivot table chart

At last, we add a table chart as described below:

  • Data source: Your blended data
  • Dimension: Metric name
  • Metric: the calculated field formula below named as value
  • Sort by Metric name
Transposed table chart

Or we can create a pivot table chart if we need several columns of dimension values:

  • Data source: Your blended data
  • Row dimension: Metric
  • Column dimension: Month of Year
  • Metric: the calculated field formula below named as value
Configuration of pivot table to transpose data

Calculated field to use as charts metric

Value
CASE 
 WHEN Metric name = "Pageviews" THEN Pageviews
 WHEN Metric name = "Sessions" THEN Sessions
 WHEN Metric name = "Users" THEN Users
 ELSE 0
 END

Config review

Voila. At last, let’s review the full configuration with a small video.

“Surtout, ne te retourne pas…”