Tableau - Calculate profit when cost and revenue are in same column

Question

I want to calculate profit when cost and revenue are in the same column. For example:

 item   account_category   balance
   x                cost         5
   x                cost         5
   x             revenue        12
   y                cost         8
   y             revenue         7

So, in Tableau, I'd like to have calculation that gives me profit both in terms of absolute dollars (i.e., revenue - cost) and percentage of revenue (i.e., (revenue - cost)/ revenue). I'd then like to slice / dice by columns like item.

I'm not even sure where to start; I've tried table calculations with no success.

So you are trying to do 12-5 and 7-8 and (12-5)/12 and (7-8)/7... is that correct? — Siva
Yes, but to be clear, it would be (12-5-5) and (7-8), then (12-5-5)/12 and (7-8)/7...since its would be aggregated by item in this example. — Ryan Erwin

S. User18 S. User18 · Accepted Answer · 2018-02-24T19:09:29

Profit:

SUM(IIF([account_category] = "revenue",[balance],NULL))
-
SUM(IIF([account_category] = "cost",[balance],NULL))

% of Revenue:

(
    SUM(IIF([account_category] = "revenue",[balance],NULL))
    -
    SUM(IIF([account_category] = "cost",[balance],NULL))
)
/
SUM(IIF([account_category] = "revenue",[balance],NULL))

To create the view:

The level in the view does not matter (i.e. here is total profit, % of Revenue):

Tableau - Calculate profit when cost and revenue are in same column

2 Answers