[b]How to calculate a Conditional Average by Branch[/b]

[Nassy]

Hi everyone,

The Report Details

I have created a report which shows a variety of totals of property-related transactions shown per branch. These totals are shown for the current week and the month to date

What I am trying to do

I am trying to create a summary field which will display the Average Sale Price per branch for both current week and month to date.

For example to calculate the Average Sale Price for the Current Week I have created the following formula:

[color]
if {KPIDetails.Z}='Agreed Sale' and {@Current Week}='Current Week'
then Average({KPIDetails.Amount},{KPIDetails.Branch})
[/color green]

The problem

I have created a Group on the Branch and it is on the Group Header that I place all my summaries. However I cannot get the Average summary to display. All the values come up as £0.

I am tearing my hair out trying to find a solution to this problem so any help much appreciated


Nassy

 

[MisterMo]

Just for testing purposes have you tied to place the average at the Group Footer?

Mo

 

[lbass]

Change your formula to:

if {KPIDetails.Z}='Agreed Sale' and {@Current Week}='Current Week'
then {KPIDetails.Amount}

Then insert an average on the formula at the branch group level.

-LB