Use AVG in Totals in List options

[cjbates]

I have a question about the way you are calculating total amounts for averages on groupings. I am using AVG to calculate an average amount for a report with multiple groupings. At the top most group, the AVG amount appears to be the average for all of the records in the dataset. But when I drill down, there is a subtotal AVG which in my mind should match the upper level grouping (see attachment). However, it is an average of the average amounts for each of the rows in my next grouping level (do the math and you can see that the 452 in the drill down subtotal is an averate of the 5 rows (add up the AVG HAP in the 5 rows and divide by 5) instead of an overall average that matches the average in the top most grouping. Intuitively I would excect the totals on the subgrouping to match the row values on the next highest grouping and they do for othet total types like Sum or Count, but not for AVG.
Furthermore, I can suppress the Column Grand totals using List Options, but this does not suppress these totals in the lower groupings. How do I hide this incorrect overall AVG figure in the lower half?
Thanks
Cynthia

[Bob Cergol]

Cynthia,

Can we see how you made the average?
Did you make you own plus column and specify the column type as "Total formula"?
Or did you add an existing column to totals, and set its type to "avg."?

Bob

[Bob Cergol]

Cynthia,

The screen shot below shows how Dynamic AI's "avg" total type simply averages the values in the column.

I think what you want to do is make a +column as a "total" type and then the calculation will be made at the actual level of the data, i.e. calculate at the group total level, etc.

Avg.Sale = Amount used as total and type set to "avg.(This is avg. the numbers in the column which are avg. themselves!
Avg.Amt. = +Column using Amount divided by count. (This is the avg. you want.)

Its easier to learn the syntax for some of these formulas by using the on-screen formulat editor to make them!

Avg_Total_Types.png (78.33 KiB) Viewed 32760 times

Bob

[cjbates]

Hi Bob,

I think understand what you mean by calculating the avg....but my formula is not working (Im getting no results). Please look at my screen shot and tell me what is not correct? Thansk.
Cynthia

[Bob Cergol]

I guess you must be getting an error because I see an extra + sign in the formula.
I think this is probably what you want:
( [[tr_hap]] + [[tr_uap]] ) / [[# Tenants@COU]]
Bob

[cjbates]

Hi Bob, I removed the extra + but still receive no results (field is blank, there is no error nor is there a 0).

Cynthia