I am trying to present on a dashboard the averages of the sums that are grouped by filter values.
| Month | Project Name | Time |
|---|---|---|
| January | X | 10 |
| January | Y | 20 |
| January | Z | 8 |
| February | X | 4 |
| February | Y | 10 |
| February | Z | 12 |
| March | X | 13 |
| March | Y | 15 |
| March | Z | 1 |
So given the above example I'd like to use superset to show the sum of time spent on the project, which is straightforward when I add one project to the filter box, but when I'd like to add another project I need to take the average of the sum of projects selected in a filter box.
And that's when things get tricky. If I use average as a metric I will only get averages from the Time column, but when I use sum I would only get sum of values in Time column.
Does anyone know how to combine both in order to use filter box and specify project X and get the sum (27), but when I add another value to the filter box let's say project Y, than I will get an average of the sums (so 36, because total time on project X is 27 and total time on project Y is 45, so I have (45+27)/2=36)?
Your metric in this case can be
SUM("Time")/COUNT(DISTINCT "Project Name")