This has been bugging me for a while now. In this kind of example data, I would need to get the product with the highest revenue within a group populated in a column and also the revenue of that product. What I have achieved so far is only get the values populated on the matching rows, but not on the others.
I cannot use any precalculation because, like in the example, there is an inputfield involved.
The following table shows the correct result.
| Group | Product | Revenue | Product with most revenue in group | Revenue of the product with most revenue |
| A | A1 | 5 | A2 | 10 |
| A | A2 | 10 | A2 | 10 |
| A | A3 | 5 | A2 | 10 |
| B | B1 | 20 | B1 | 20 |
| B | B2 | 20 | B1 | 20 |
| B | B3 | 5 | B1 | 20 |
I only have the bolded values in my table at the moment with the following style expressions:
=Aggr(
if(rank(sum(QTY * Price), 4, 1) = 1
, Product
)
, Group, Product
)
