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

    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!