4 Replies Latest reply on May 9, 2018 3:23 AM by Ashwath Raj

    Difference between profit/Sales and Sum(Profit)/Sum(Sales)

    Ashwath Raj

      Kindly let me know how the aggregation works

        • 1. Re: Difference between profit/Sales and Sum(Profit)/Sum(Sales)
          Simon Runc

          hi Ashwath,

           

          Good question...

           

          So Profit/Sales is a row level calculation...so this calculation is performed for each row of the data. If you then bring it into the canvas you can then choose how it's aggregated (SUM, AVG....) If you look at the below table, I've done this and the SUMed up the result in the total row

           

           

          with the SUM(profit)/SUM(Sales) the SUM is done first, and then the division. So in this example it would be like this

           

           

          Hope that helps, but let me know if that's not clear.

          • 2. Re: Difference between profit/Sales and Sum(Profit)/Sum(Sales)
            Ashwath Raj

            Hi Simon,

             

            Thank you for the reply. I understand the scenario you used.. But i am having trouble understanding following scenario. I have sub categories and corresponding  sales. Dont mind the column name sum(profit/sales).. I have actually given it as sum(profit)/sum(sales). So you can notice that values are different for profit/sales column where i have used [Profit]/[Sales] and sum(profit)/sum(sales) . Can you pls explain on that sgfd.PNG

            • 3. Re: Difference between profit/Sales and Sum(Profit)/Sum(Sales)
              Simon Runc

              So what's happening here is that the "grain" (or "granularity") of the data (for SuperStore) is by Product/Order....so the SubCategory Accessories, is made up of lots of rows of data. When you use the Profit/Sales the calculation is performed for each row, and then when you bring it into the canvas (I assume it has a SUM aggregate around it) it is SUMing up all the Profit/Sales for each row that makes up Accessories (which is why we get a silly value).

               

              In your SUM(profit)/SUM(sales) all the rows for Profit are SUMed up, and then All the rows for Sales are SUMed up and then the division is done to get the margin. If you look at the pills in your table you'll see that the Profit and Sales have a SUM() aggregate, so the SUM(profit)/SUM(sales) (for Accessories) is doing 41,937/167,380 = 25.05%

               

              Does that make sense?