I have a report in which i have to calculate average for a specific base criteria.
e.g
Location of Action | Action Performer | Count of Actions Performed
US| Employee 1 | 5
US| Employee 2 | 10
US| Employee 3 | 15
================
Total: 3 | 30
My report is much more detailed (flat) but i am displaying only performer level details even when i have details per performer action level details too.
Average of Action/Performer = (5+10+15)/3 = 10
I need to add additional column to this report for comparative analysis as follows
Location of Action | Action Performer | Count of Actions Performed| National Average of Activities
US| Employee 1 | 5 | 10
US| Employee 2 | 10| 10
US| Employee 3 | 15| 10
================
Total: 3 | 30
i have tried using the following formula's but non seem to give me what i expect
AVG
SUM( Count of Actions Performed) / Count Distinct (Action Performer)
But in all the expressions the issue seems to be with denominator's.
I am able to get SUM( Count of Actions Performed) as 30.
But when I calculate Count Distinct (Action Performer) it gives me for each row count as 1 not total table count of 3.
How do i achieve this ?
