6 Replies Latest reply on Apr 30, 2018 11:36 PM by mortenbodaugaard.jrgensen

# P&L ratio caluclations

Hello,

I have attached my workbook. In my data set, I have a column for account name which I then grouped by account type. However, I want to be able to calculate various ratios  based on the PL group column. Is my data in the proper format to do? If not, what is the best way to change the layout.

Thanks,

Sean

• ###### 1. Re: P&L ratio caluclations

The way you have it will work, the unpivotted version would also work

It all depends on how you want to write the calculations.

What are some examples with what you want to do ?

1 of 1 people found this helpful
• ###### 2. Re: P&L ratio caluclations

Hi Chris,

Thanks for the feedback.

To start I am just looking to do basic ratio calcs such as gross profit, net profit, etc. Since I am new to Tableau, I know how to write basic formulas using separate column headers. What I am confused on is how to write a formula using specific accounts within my amount column such as total cost of sales/total sales?

• ###### 3. Re: P&L ratio caluclations

Looking at your data you should do something like this:

//Cost of sales

SUM( IF [PL Group]='Cost of Sales' THEN [Balance] ELSE NULL END)

//Total Sales

SUM( IF [PL Group]='Sales' THEN [Balance] ELSE NULL END)

and then do [Cost of Sales] / [Total Sales]

Do note that the "ELSE NULL" part of my calculations is the default behavior for an IF statement and can be omitted.

Also note that any filter on PL Group that removes the group you refer to in the calculation might produce unwanted results.

1 of 1 people found this helpful
• ###### 4. Re: P&L ratio caluclations

Thank you. Since I am a novice, would you be able to show me the construction in the calculation field within Tableau?

Sean D. Penner, MBA, CPA

Chief Financial Officer

Carubba Collision

716-834-0466 ext. 155

• ###### 5. Re: P&L ratio caluclations

1 of 1 people found this helpful
• ###### 6. Re: P&L ratio caluclations
1 of 1 people found this helpful