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.
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
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?
GOT IT Thanks a ton