CODEWITHSUNDEEP
tableau-api
Ryan Erwin
Ryan Erwin

Reputation: 817

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

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.

Upvotes: 0

Views: 1345

Answers (2)

S. User18
S. User18

Reputation: 712

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:

enter image description here

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

enter image description here

Upvotes: 1

Siva
Siva

Reputation: 9101

Try below Steps:

Place Item and Account Category on Rows and then uncheck Show Header of account category.

Add balance to the sheet and apply table calculation for difference and execution should be Pane Down Or Create a calcualted field and use below code:

ZN(SUM([Balance])) - LOOKUP(ZN(SUM([Balance])), -1)

Now create one more formula and use below code for Percentage Calculation

[Profit]/SUM([Revenue])

Filter the rows that are null.

Your report would be like below:

enter image description here

I can share the report if you need.

Upvotes: 0

Related Questions