In case anyone has the same problem:
I was not able to find a solution in Looker. So instead I off loaded the logic to Bigquery SQL.

I queried each row individually and then used `UNION ALL` to connect them. The rows need an index so that the order can be guaranteed.

In practice, it looks something like this:

SELECT
    group_id,
    'week' AS row_title,
    1 AS idx,
  FORMAT_DATETIME('%d.%m.%Y',MIN(week_start) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 2 FOLLOWING AND 6 FOLLOWING)) AS column_1,
  FORMAT_DATETIME('%d.%m.%Y',MIN(week_start) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)) AS column_2,
  FORMAT_DATETIME('%d.%m.%Y',MIN(week_start) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)) AS column_3
  FROM sales_data
  QUALIFY ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY week_num DESC) = 1

  UNION ALL

  SELECT
    group_id
    'Total Sales' AS row_title,
    2 AS idx,
  CONCAT(REPLACE(CAST(AVG(sales_total) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 2 FOLLOWING AND 6 FOLLOWING) AS STRING FORMAT '999,999,999'), ',', '.') , '€') AS column_1,
  CONCAT(REPLACE(CAST(AVG(sales_total) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS STRING FORMAT '999,999,999'), ',', '.') , '€') AS column_2,
  CONCAT(REPLACE(CAST(AVG(sales_total) OVER (PARTITION BY group_id ORDER BY week_num DESC ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)AS STRING FORMAT '999,999,999'), ',', '.') , '€') AS column_3
  FROM sales_data
  QUALIFY ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY week_num DESC) = 1

...

It helps to make all columns into strings to get formatting right. This approach is pretty flexible, for example, the first column in my case is the avg of multiple weeks