Custom Average Calculation in Report

user10122951

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 ?

Gianni Ceresa

You must tell OBIEE that you would like the COUNT DISTINCT not at the row level but at the "US" level.

So you can try by using the AGGREGATE(... AT ....) function if you have a hierarchy containing your "Action Performer" and its upper level, or try by adding a : by "The column name of the column with the US value"

In this way you move the COUNT DISTINCT to the upper level and it will give you 3.