5 Replies Latest reply on Oct 19, 2015 7:36 AM by Justin Thacker

# Calculate Gross Profit and EBIT in table?

I have a data set with an account code hierarchy that I am trying to run some trends, etc. on.  However there is also a desire to provide a table with the summary P&L on the sheet as well.  I cannot figure out how to do this with the data set I have.  The flat file I'm using does not have Gross profit, ebit, pbt flags.  I only have revenue, cogs, sg&a, etc.

I want to calculate say Gross Profit and EBIT from the data set attached (small sample)

GP is Group 1 = "Revenue" - Group 1 = "Cost of Revenues"

and so on...  I want it to show up as a row beneath Revenue and Cost of Revenues.

• ###### 1. Re: Calculate Gross Profit and EBIT in table?

Hi Justin

As with many things in Tableau - there are many ways of achieving what you need. Assuming that you cannot restructure the data? (I'm guessing its coming out of SAP)

The attached is one way of achieving what you need..... essentially - you are creating new measures that act as the different P+L items.... Revenue, Cost of revenue, Gross Profit - these can be done that are created using conditional calculated fields to create the right numbers. I've only done the logic for Revenue, Cost of Revenue, Gross Profit.... but should give you an idea.

Hope that helps

• ###### 2. Re: Calculate Gross Profit and EBIT in table?

You can use:

Revenue:

{include [Group 1]:sum(

if ([Group 1])="Revenue" then ([Amount - Actual]) else 0 END)}

Cost of Revenues:

{include [Group 1]:sum(if ([Group 1])="Cost of Revenues" then ([Amount - Actual]) else 0 END)}

Gross Profit:

[Revenue]-[Cost of Revenue]

• ###### 3. Re: Calculate Gross Profit and EBIT in table?

I was going down this path initially.  The issue was / is that when I try to pull the dimensions over it shows revenue, cogs and GP for each line:

Column 1     Column 2

Revenue      Revenue

COGS

GP

COGS          Revenue

COGS

GP

• ###### 4. Re: Calculate Gross Profit and EBIT in table?

Hi Justin

Can you just clarify.... those dimensions you refer to.... why would you want to show those over different PnL lines.... I'm a little confused as to why you'd need to see Gross Profit broken down by those dimensions.... by creating the calculated fields you are supeseding the need to break down those dimensions at all for your PnL view?

Thanks

• ###### 5. Re: Calculate Gross Profit and EBIT in table?

I'd like to see revenue by type (there are four types of revenue in the total data set) total up to Total Revenue; Cost of revenue by type total up to Total Cost of Revenue; and GP by Type.

I want that next level of detail on type of revenue, etc.