Skip to Content
0
Former Member
Jan 04, 2008 at 10:50 PM

Dynamic Average Calculation

102 Views

Hi,

I have a requirement to calculate average dynamically. I need to calculate the average number of service tickets per 'product line'.

so report shud look like as follows.

Number of tickets Average Tickets

PL1 20 15

PL2 10 15

PL3 15 15

Total 45 15

Now say a drilldown is added with employees and the reprot looks as follows:

Number of tickets Average Tickets

PL1 David 18 10

PL1 John 2 10

Total 20 10

PL2 Rita 7 5

PL2 Jay 3 5

Total 10 5

and so on......

The average calcualtion depends on the characteristic in the rows and the number of values each drilldown combination has. As a result i have not been able to use Exception aggregation. I also do not see 'Average' available in the calculations tab for the KF/Formula properties (there is only weighted average). I am using BI 7.0

I tried to create a dummy KF Count with '1' in the formula and then use the calculation SUMCT 'total tickets'/ SUMCT 'Count'. But it does not seem to be working for 'Count'.

Let me know what I am doing wrong or if there is any other way.

Thanks

Gaurav