How to calculate a procentual difference between two columns in Data Studio pivot?

Question

I have a data source table in Google Sheets, looking like:

+------------+--------------+--------+
| Date       | Search query | Clicks |
+------------+--------------+--------+
| 09.11.2020 | keyword 1    | 20     |
+------------+--------------+--------+
| 16.11.2020 | keyword 1    | 24     |
+------------+--------------+--------+
| 16.11.2020 | keyword 2    | 23     |
+------------+--------------+--------+
| 09.11.2020 | keyword 2    | 18     |
+------------+--------------+--------+
| 09.11.2020 | keyword 3    | 19     |
+------------+--------------+--------+
| 16.11.2020 | keyword 3    | 17     |
+------------+--------------+--------+

With this data source i have a Data Studio pivot, looking like:

+--------------+------------+------------+
| Search query | 09.11.2020 | 16.11.2020 |
+--------------+------------+------------+
| keyword 1    | 20         | 24         |
+--------------+------------+------------+
| keyword 2    | 18         | 23         |
+--------------+------------+------------+
| keyword 3    | 19         | 17         |
+--------------+------------+------------+

How can i create an additional column in Data Studio with calculation of procentual clicks difference between dates? So the Data Studio table will look like:

+--------------+------------+------------+---------------------------------+-----------------------------+
| Search query | 09.11.2020 | 16.11.2020 | Difference between B and C in % | Formula for Difference in % |
+--------------+------------+------------+---------------------------------+-----------------------------+
| keyword 1    | 20         | 24         | 17                              | =100-((B2*100)/C2)          |
+--------------+------------+------------+---------------------------------+-----------------------------+
| keyword 2    | 18         | 23         | 22                              | =100-((B3*100)/C3)          |
+--------------+------------+------------+---------------------------------+-----------------------------+
| keyword 3    | 19         | 17         | -12                             | =100-((B4*100)/C4)          |
+--------------+------------+------------+---------------------------------+-----------------------------+

Last column contains just formula example.

I tried all available possibilities in Data Studio, but failed. The cause of my fail is maybe a bug i've experienced.

My other try was to build a pivot in Google Sheet directly and calculate the difference there - but this doesn't work for me too - because my Google Sheet breaks pivot when it renews its data.

Answer 1

The key is calculated field. For each pair of columns, where you need a difference, you create a calculated field. Then you add this field to your table and see the calculated difference. Like on the following screenshot.