You need to keep Acct Category and Category in different axes if you want to use the 2 values in Acct Category for the purpose of computing profit margin.
Here's what I did:
1. Remove "Profit Margin" from Rows
2. Move "Acct Category" from Rows to Columns
3. Click Analytics, drag Totals to your table, choose Column Grand Totals
Now you have your totals per Acct Category, and the last thing that remains to be done is compute profit on a row-by-row basis. This is done using Table calculation.
4. Duplicate Amt
5. Rename the copy obtained above to ProfitMargin (observe no space; if you want to use space, delete the "Profit Margin" you defined first)
6. Drag Profit Margin to Text
7. Click on SUM(Profit Margin), choose Quick Table Calculation - Difference
You get the margins you need - now you can format them a bit to beautify the presentation.
8. Click Text, click the "...", replace what you see with <SUM(Amt)> <Difference in SUM(ProfitMargin)> (note the tab between the <SUM(Amt)> and <SUM(ProfitMargin)>; Tableau doesn't allow you to insert tab directly, but you can copy-paste it from another editor). Now make sure your text is left-cenetered.
9. Right-click on Cogs column in the table, choose Edit Axis, replace it with "Cogs Profit Margin"
You get the idea - you can continue formatting it as you like.
I'm attaching my result - Let me know if you have any questions.
book1 (1).twbx 520.0 KB
Your option seems like a good work around with the only exception that it modified the format I had originally wanted. Doing some further research, I came across a good article that describes how to modify the Grand Totals only, yet keeping the two categories in the Rows shelf.
The article below shows how to Customize Grand Totals. I will give it a try and upload a workbook with the desired results.
I tried the solution from the Customized Grand Totals article and it worked. I'm uploading the workbook to see if you can identify potential issues or improve the calculated field (Gross Margin) I created, again using the concepts from the article above. The calculated field formula I used display as follows:
if MIN([Acct Category])!=MAX([Acct Category]) THEN
SUM(IF([Acct Category]="REVENUE") THEN [Amt] END) - SUM(IF([Acct Category]="Cogs") then [Amt] END)
SUM( [Amt] )
Then I formatted the Grand Total label and replaced it with Gross Margin.
Customized Grand Total.twbx 531.8 KB