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
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
Now for sales per case, you can simply create a table calculation -> total_sales/total_cases
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?