Suppose I have a KPI, average time to fill, with a target 30 days. I would like to be able to identify the top 3 values across all dimension which have the highest average time to fill. Ideally, as a funciton.
So for example, suppose we have three dimensions, Country[USA, UK, Mexico, Canada], Age[10-15,16-20,21-25], and Gender [M,F]. We find that when we average by demographic value, USA, 16-20, and 21-25 have the top 3 highest average time to fills across all dimension values. How would we go about identifying this? I will display this information next to the kpi as an "insight", rather than the person having to go through every single dimension.
Coming from Python, I would create a dict (or a 2 columns) that stacks all of the average time to fills by each dimension values as a key:dict pair, and then pull the top 3. I was thinking maybe similar here, where 2 columns are created : demographic value, and average time to fill, and then firstsortedvalue function is used, but unclear on how to create the columns or if there is a better way of doing this via a function which would be ideal because I would like to still be able to use the date filters. Thanks!