Google Data Studio

Google Data 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 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.

Google Data Studio - Transpose table chart

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, create a simple Google Sheets with one tab containing the list of metrics and add it as an embedded data source in your report.

Metrics list as data source

2. Blended data

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

  • As left data source
    • Data source: Main data source, here Google Analytics data source
    • Join keys: None
    • Dimensions: Month of Year
  • Metrics: Users, Sessions, Pageviews
  • As right data source
    • Data source: Google Sheet data source with metric names
    • Join keys: None
    • Dimensions : Metric (yes, I know…quite confusing… 😉 )
Blended data

3. Table chart or pivot table chart

At last, create your table chart as described below:

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

Or create a pivot table chart if you 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
Pivot table chart

Calculated field to use as charts metric

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

“Surtout, ne te retourne pas…”