subtotal grouping row dimension table

[alexandernatale]

Hi everyone,

I have a strange problem, I hope someone can help me!

I have a table made like this:

What I would like to have is a grouping like this:

Area 1: plant 1
Area 1: plant 2
Area 2: plant 3
Area 3: plant 4

Since it's an impromptu analysis, I don't want to change the data structure.
I tried with different formulas like "pick" and "match" and "aggr" but I didn't get out.

Can someone help me?

Thanks!

[henrikalmen · Accepted solution]

I think the best thing for you is if you can include the Area dimension in your data model, basically so that you have a table that looks something like
plant, area, value
Plant1, Area1, 994
Plant2, Area1, 709
...

Then you could just choose what dimension to use in a table chart.

But if you really want to do it in frontend, you could try this as a calculated dimension (but you wouldn't want to do this with many rows):

if(match([plantfield], 'plant 1', 'plant 2'), 'Area 1', if([plantfield]='plant 3', 'Area 2', if([plantfield]='plant 4', 'Area 3', 'unknown area')))

[alexandernatale]

Thx @henrikalmen  it's perfect for me!

---

if(match([plantfield], 'plant 1', 'plant 2'), 'Area 1', if([plantfield]='plant 3', 'Area 2', if([plantfield]='plant 4', 'Area 3', 'unknown area')))

---

in this way I don't modify the data structure as this classification is "temporary" and ad hoc for some visualizations and reports!