I have two items that I want to compare on a few different metrics. Unfortunately, the Table look seems to only allow the pivot dimension on top of the measures. While I understand how this limitation may seem logical, it makes it quite difficult to compare the pivoted columns, which somewhat defeats the purpose of pivoting in the table.
This is my data table in BigQuery
Date | Item | Metric 1 | Metric 2 | Metric 3 |
January | A | 9 | 33 | 200 |
January | B | 100 | 66 | 400 |
February | A | 9 | 33 | 200 |
February | B | 100 | 66 | 400 |
March | A | 9 | 33 | 200 |
March | B | 100 | 66 | 400 |
I can load this in Looker and easily pivot this to:
A | B | |||||
Date | 1 | 2 | 3 | 1 | 2 | 3 |
February | 9 | 33 | 200 | 100 | 66 | 400 |
January | 9 | 33 | 200 | 100 | 66 | 400 |
March | 9 | 33 | 200 | 100 | 66 | 400 |
But what I really want to do is compare A and B, not 1 2 and 3 within A:
1 | 2 | 3 | ||||
Date | A | B | A | B | A | B |
February | 9 | 100 | 33 | 66 | 200 | 400 |
January | 9 | 100 | 33 | 66 | 200 | 400 |
March | 9 | 100 | 33 | 66 | 200 | 400 |
However, this layout of the table seems impossible in Looker without completely restructuring the data into long/tidy format and then doing a complicated join. I know how to do this in Excel/Sheets and Tableau, so I am surprised it's so difficult in Looker.
Is there a way to show the third table from the first table? The second table is very user unfriendly for this use. I am using Looker Core.