2 Replies Latest reply on Apr 24, 2018 10:02 AM by Phil Whelan

# Gross Margin and other percentage calculations

Hi,

I have created a workbook on Tableau which  contains various financial tables - such as P&L, Cash Flow, expenses, revenue breakdown, aged debtors, and so on. The workbook is fed by four different data sets (Grp CF Full, Grp IS Full, INT Func Full Values and Int Rev Mark).

I am however struggling to calculate percentages, i.e. those relating to percentage of sales and working capital metrics.

I have created several calculated fields on Tableau (such as Actuals vs. Budget or Forecast), but I am struggling with creating calculations for the above fields - since they all depend on selecting items within dimension hierarchies, rather than measures. I have browsed through several similar questions on this forum, but can't find an appropriate answer.

I have attached a dummy packaged workbook here in Tableau v10.5 showing my dataset (can't attach the real workbook due to data confidentiality).

Since this is the first time I have asked a query on this forum, please do advise if the workbook has not attached correctly.

Essentially I have three main calculation types I'd like to do:

1.    "Act IS" tab -   I need to create rows for various items as a percentage of sales, so just need to divide each metric by sales - namely:

-     Gross Margin    (Gross Profit/ Sales)

-     Direct Labour as % of sales

-     Factory OHs as % of sales

-     Materials as % of sales

-     Other COS as % of sales

-     Warranties as % of sales

2.     "Act WC metrics" tab -   I need to create rows for the following working capital metrics;

-     DSO                (calculation:          Ave Debtors 12 mths/ Rolling Gr Sales 12 mths * 365)

-     DPO                (calculation:          Ave Creditors 12 mths/ Rolling COS 12 mths * 365)

-     Stock Turns     (calculation:         Rolling COS 12 mths/ Ave Inventory 12 mths)

3.    Cash conversion - I need to divide DHOC (in the "Grp CF Full" data set) by DHOP (in the "Grp IS Full" data set). I appreciate this may be tricky as the metrics are in different data sets.

Many thanks in advance for your help with this.

• ###### 1. Re: Gross Margin and other percentage calculations

Using your data in the way that it's formatted at the moment, you would need to do this:

1. For "Act IS", create values in the [Income Statement Level 1] dimension - just the dimension value is fine and then you can do the calculation in Tableau.

2. Create values in [Cash Level 1] so you can do the calculation in Tableau.

Normally I would say that Gross Profit, Sales, Gross Margin etc are Measures and if you do it that way it's a simpler process but you can make it work with the data that you've got, you just need to create the dimension values outside Tableau to create the "slot" for that calculation.

• ###### 2. Re: Gross Margin and other percentage calculations

Thanks, I will take a look at this method.