I have a Straight Table for which I'm trying to add a calculated field that is based on another dimension, one which is not part of the straight table.
Essentially, the dimension on the table is customer name. I want to add a column which shows, for each customer, the average profit margin per sale, where profit margin per sale is the total profit / total cost.
To illustrate, below is the data for a single customer:
When we calculate over the dimension of invoice, I get that Invoice A had a profit margin of 1.83 (55/30), Invoice B was 1.57 (55/35), Invoice C was 5.0 (25/5) and Invoice D was 1.0 (10/10).
The average of these margins, for this customer, is 2.35, which is the number I'm trying to get in my other table. If we just add the costs and the profits, and calculate the margin, we come up with 1.81.
The equation I tried writing was as follows (on a straight table with dimension customer), but it doesn't quite work:
This gives a list of values of average margins for each Invoice, and if is used with Customer as dimension, you need avg() to aggregate. If is used with Invoice as dimension it 'works' but the aggr() is useless.
=aggr(sum(Profit) / sum(Cost), Customer)
This gives a list of values of weighted average margins for each Customer, whereas Elie asked about simple average of margins of invoices for each customer.