Struggling with straight table totals

[#1]

Hello,

I need to calculate Totals on a straight table.

Sample straight table -

Product	Total sales	Total cost		Margin
	1000	800		-
Product1	500	400		20%
Product2	300	250		17%
Product3	200	150		25%

As a source I have 2 tables -

1. Sales (with Product IDs, quantities and sale prices)

2. Inventory (with unique Product IDs and costs)

Product ID is a dimension.

Total Sales is simply a sum of Sale prices from Sales table

Total Cost is sum (Qty (from sales table)) *Cost (from Inventory table)

Margin is (Sale - Cost) / Sale

Now total margin column is for some reason blank. It shouldn't be a sum of all margins but (Total sales - total cost) / total sales

How can I do that?

I read something about aggr function but not sure I understand how it works.

Many thanks!

[vgutkovsky]

Your logic is correct, so the only reason it may not be working is some sort of syntax error. Make sure your expression look like this:

Sales

sum(Sales) //substitute your actual field name

Cost

sum(Qty)*sum(Cost) //substitute your actual field names

Margin

("Sales" - "Cost")/("Sales") //note that this uses the expression titles set above--they should match exactly

Regards,

Vlad

[CELAMBARASAN · Accepted solution]

Hi

This may be because of expression problems.

Please check the attached the file and check this is what you have written as expressions.

[#4]

great it worked. it looks like I had an error in my formula. Thansk!