I have the following table where I get every week in two free text fields a color and the reasoning, i.e. why they choose the color.
database database
In Looker Studio I derived from column B the color via adding a new field with the following formula, for every color that is possible to choose from (low fixed cardinality):
SUM(IF(CONTAINS_TEXT(LOWER(Color),"blue"),1,0))
And voilà I get the following graph...
...that shows me the colors per cw ...that shows me the amount of colors per week
I added a table, similar to the one shown above, in order to be able for the viewer to deep dive on the reasoning. My idea was: when clicking on a CW and a specific color, the table below should adjust and only show data specific to that CW and color, e.g. yellow in CW 25. Unfortunately it does not matter what color I click in a CW, the table will only filter data based on the CW, not on the color.
How can I achieve to filter on CW and color when clicking on a certain color in a CW? I assume it has something to do with the way the data is structured and that I derive the final color from column B. But without the formula I would not be able to count the appearances of the color. So in my head I am stuck in a loop.
Thanks for the support 🙇🏻