I need to perform an average of averages to get this KPI at separate levels, am struggling to do the same with some of the calculated field limitations. Below is the step by step process on how its usually done on excel that needs to be replicated on quicksight. Would appreciate any help.
Data Set Structure -
The data set is in the below format, where I have count1 and count2 at a city-location level. Avg is obtained by dividing Count1 and Count2 and converting it into a percentage format
| City | Location | Count 1 | Count 2 | avg |
|---|---|---|---|---|
| a | a1 | 50 | 100 | 50% |
| a | a2 | 70 | 100 | 70% |
| a | a2 | 20 | 100 | 20% |
| b | b1 | 40 | 100 | 40% |
| b | b2 | 50 | 100 | 50% |
Step 2 -
Avg2 needs to be calculated by doing an average over city (There is no issue here as a simple avgOver works fine)
| City | Avg2 |
|---|---|
| a | 47% |
| b | 45% |
Step 3 -
Avg3 needs to be obtained by doing a simple average of a and b from the previous step (and not the averages of the location a1-b2). An avgOver function will perform the averages of the locations from the base dataset and not the derived average from step 2
Thanks in advance!