Is there a way to a calculated field ignore only one filter and consider the others?

I would like to make a calculated field which is a fixed percentage of itself, aggregated by category AND respect the others filters of date, company and product.

date company category product value
2022-01 AA A p1 10
2022-01 AA A p4 10
2022-01 AA B p1 3
2022-01 AA C p2 23
2022-01 BB A p1 6
2022-01 BB A p4 7
2022-01 BB B p1 11
2022-01 BB C p2 21
2022-02 AA A p1 1
2022-02 AA B p1 5
2022-02 AA C p2 16
2022-02 BB A p1 5
2022-02 BB B p1 9

I created the calculated fields:

  • value by PRE_FILTER = sumOver(value, [date], PRE_FILTER)
  • value by PRE_AGG = sumOver(value, [date], PRE_AGG)

For example, when I filter company = “AA” in January 2022, value by PRE_AGG gives me the expected answer of 46. In February also gives me the expected answer of 22. I would like to fix these totals whenever I filter the category at the same time it is dynamicaly filtered by the others filters of date, company and product.


1 Like

I’m sharing your feature request with our product team

1 Like

Hi elrafaraujo! I think I have a solution for you if you dont want to display the Category field in your table. The solution will still give you two Value fields - one that is filtered by Category and one that is not (for your denominator I suppose), but it wont show the Category field in the visual (I mean you could keep it there, but when you ‘filter’ by category, all the categories would still be visible).

Let me know if that’s acceptable and Ill share it here.

I guess more generally it would be helpful to understand what the end-state visual should look like.

@emilyzhu We might want to consider to support field level filtering.

1 Like

Hi @Jesse, could you show your solution in that case? I have the same problem here. Tks

It has been a while since I dove deeply into this, but I found the analysis I and @Koushik_Muthanna were testing this out on. Check this out in DemoCentral (can open the Analysis and look at calcs, filters, etc) to see if it meets your needs. There are a few different sheets with different approaches.

1 Like

Thank you @Jesse . let me take a look and back here soon.

1 Like

@woliveiras Did Jesse’s solution work for you? Hope so.

1 Like

Hi @Kristin … sure. It was useful. Thank you

1 Like