4 Replies Latest reply on Aug 5, 2016 3:49 PM by Andrew Watson

# Profit Loss Calculation

I am having the data like below as dimension and Measures. How to display it in view

Revenue/Expenditures
Revenue Item
Date
Value
RevenueRevenue from Food Item01-Jan-2010100
RevenueRevenue from Vegetables01-Jan-2010200
RevenueRevenue from Electronics01-Jan-2010
300
ExpenditureExpense from Salary01-Jan-2010
200
ExpenditureExpense from Rent01-Jan-2010
100
RevenueRevenue from Electronics01-Jan-2011
1000
RevenueRevenue from Vegetables01-Jan-20112000
RevenueRevenue from Food Item01-Jan-2011
3000
ExpenditureExpense from Salary01-Jan-20114000
ExpenditureExpense from Rent01-Jan-20113000

I would like to see the report in Tableau as like below. Please let me know how to do it?

Revenue/Expenditures
Revenue Item
Year 2010
Year 2011
RevenueRevenue from Food Item1001000
Revenue from Vegetables2002000
Revenue from Electronics300
3000
ExpenditureExpense from Salary200
4000
Expense from Rent100
3000
Profit / Loss (Revenue - Expenditure)300-1000
• ###### 1. Re: Profit Loss Calculation

It might be easier to put Profit Loss on it's own worksheet such as I have done in the attached.

• ###### 2. Re: Profit Loss Calculation

You can do this with some formatting tricks. First you need to create a new field which returns your expenditure as a negative number.

IF [Revenue/Expenditures] = 'Revenue' THEN [Value] ELSE [Value] * -1 END

This is the field you will drag into your table as the new value.

Drag the Date into the Columns shelf and set it to be a discrete (blue pill) year. The other 2 columns are dragged to Rows. Switch on the column grand totals (Analysis - Totals) and you'll see you get your table as you want it, although you want a different grand total label. Next up you want to change the title of Grand Total and format the numbers so the non-total negative numbers are displayed as positive.

This is where the formatting comes into play. Right click the Grand Total and Format. You can change the label.

Next format your new value field. You'll notice that both the Default and Grand Totals can be formatted separately:

Apply a Custom format to the Default, removing the - sign:

Format the Grand Totals part so the minus sign is there:

The end result will be (where you might need to sort to lay it out in the order you want):

1 of 1 people found this helpful
• ###### 3. Re: Profit Loss Calculation

Thanks Andrew. It is helpful.  Is it possible to calculation based on the Dimension value "Revenue" and "Expenditure".

Actually I am having another dimension value "Financing"

Revenue/Expenditures

Revenue Item

Date

Value

RevenueRevenue from Food Item01-Jan-2010100
RevenueRevenue from Vegetables01-Jan-2010200
RevenueRevenue from Electronics01-Jan-2010
300
ExpenditureExpense from Salary01-Jan-2010
200
ExpenditureExpense from Rent01-Jan-2010
100
RevenueRevenue from Electronics01-Jan-2011
1000
RevenueRevenue from Vegetables01-Jan-20112000
RevenueRevenue from Food Item01-Jan-2011
3000
ExpenditureExpense from Salary01-Jan-20114000
ExpenditureExpense from Rent01-Jan-20113000
FinancingFund from Investor 101-Jan-2011400
FinancingFund From Investor 201-Jan-2011600

I would like to see the report in Tableau as like below with Financing. Please let me know how to do it?

Revenue/Expenditures

Revenue Item

Year 2010

Year 2011

RevenueRevenue from Food Item1001000
Revenue from Vegetables2002000
Revenue from Electronics300
3000
ExpenditureExpense from Salary200
4000
Expense from Rent100
3000
Profit / Loss (Revenue - Expenditure)300-1000
FinancingFund from Investor 1400
Fund from Investor 2600
Total1000

• ###### 4. Re: Profit Loss Calculation

I'm  not sure if that's possible. As far as I know the totals always go to the top of bottom and subtotals is ruled out as it totals all sections - i.e. revenue, expenditure and financing.

Tableau is far from the best tool for financial reporting, there are better options out there, even Excel.