Hi Everyone,
I have a metric based on TRx data (quarter-wise: Q1–Q4) across multiple Accounts, with dimensions including Region and Version (e.g., Forecast FY24).
I want to identify the top-performing account within each Region and Version based on the highest TRx value (either quarterly or total for the year).
I tried using the RANK function, but I’m only able to rank by grouping on a single dimension. I’m not able to rank dynamically by multiple dimensions together (Region + Version). It seems like the grouping works only for one dimension at a time.
What would be the best way to write a formula or configure this in Pigment to dynamically return the top account by both Region and Version?
Any guidance on the correct approach (ranking, max aggregation, scoped ranking, or another method) would be really helpful.
Thanks in advance!