Question

# How to dynamically calculate an average

• 3 replies
• 3706 views

• 0 replies

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

### 3 replies

Userlevel 7 +1

Can you show us your data table and the calculations?

I am assuming your data looks like this

total_sales | total_cases

\$1000 | 1000

\$2000 | 500

1. Now for sales per case, you can simply create a table calculation -> total_sales/total_cases

2. And for average sales per case, you can use sum and create a table calculation -> sum(total_sales)/sum(total_cases)

With this, your final table should look something like

total_sales | total_cases | sales_per_case | average_sales_per_case

\$1000 | 1000 | \$1 | \$2

\$2000 | 500 | \$4 | \$2

To get a margin I need to do similar calculations. I would like to define it as a ready to use measure for my users. So, is there any possibility to realise this without a table calculation?