How to dynamically calculate an average

[philarn07]

Hello,

I am trying to dynamically calculate averages.

For instance, 3 columns:

Detailed level:
Row 1: total sales $1,000 / total cases 1,000 / sales per case (price) $1 (1,000/1,000)
Row 2: total sales: $2,000 / total cases 500 / sales per case $4 (2,000/500)

Summary level:
Expected: sales per case $2 ($3,000/500)
Currently in Looker: sales per case $2.5 (average of $1 and $4)

I did create a custom measure, based on a custom dimension that calculate total sales/total cases, but it returns $2.5 as shown above, instea of $2.

Any assistance would be greatly appreciated!

Thanks