Cross filtering on dimension and metric on whole report

[Binchentso]

Hi everyone,

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 🙇🏻

[a_shah]

Hi @Binchentso - In the above approach I believe you are using IF statement in the derived table and then creating dimension on that column.. right? I tried same thing on my side and with cross filtering enabled in dashboard, while clicking on a single slice of stacked bar it filters for both dimensions in the data table view. Can you elaborate a bit more the problem you are facing?

~Ashish