How to calculate profit when cost and revenue are in same column

Question

Goal: Calculate profit when cost and revenue are in the same column.

Sample Data-set:

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

Query: How to create a calculation that gives profit both in terms of absolute cost(i.e., revenue - cost) and percentage of revenue (i.e., (revenue - cost)/ revenue).

Answer 1

You can create the following calculated fields:

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))