Looker Studio – How to transpose columns of metrics to rows inside a table chart
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.
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.
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
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
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
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.