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.
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.
