3 Replies Latest reply: Nov 15, 2012 4:21 PM by yallow83

# Struggling with straight table totals

Hello,

I need to calculate Totals on a straight table.

Sample straight table -

ProductTotal salesTotal cost
Margin

1000800
-
Product1500400
20%
Product2300250
17%
Product3200150
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!

• ###### Re: Struggling with straight table totals

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,