In Tableau, how can I calculate a Grand Total as Gross Profit from subtracting COGS subtotal from Revenue Subtotal?

Question

I cannot attached my Workbook because of confidentiality but I've attached a snapshotenter image description here to demonstrate an example of what I am trying to achieve and how the database i am connecting to is structured. Below is a summary of what I am looking to do.

• Ultimately, calculate Gross Profit as Grand Total using subtotals (Revenue-COGS). The Subtotals are fine to continue to calculate as is (Summing Total Units, Total Revenue, Total COGS). But I do not want Grand Total to sum all 3. • The 3 pills i have in my Rows Shelf are all in a Hierarchy in the following order: Acct Rollup2, Acct Rollup1, Account Name. Thus, I would like GP to scale/calculate as I collapse/expand • Additionally, I may want to add functionality to view Data by Monthly, Quarterly, or Annually so ideally solution scales with this functionality

Answer 1

Make the amount negative for COGS. Something like:

sum(if [Acct Rollup 2] = 'Total COGS' then [amount]*-1 else [amount] end)

If you don't wish to include Total Units in the totals either, I would put that as a separate sheet and then combine the two sheets in a dashboard.