1 of 1 people found this helpful
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):
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 Revenue from Food Item 01-Jan-2010 100 Revenue Revenue from Vegetables 01-Jan-2010 200 Revenue Revenue from Electronics 01-Jan-2010 300 Expenditure Expense from Salary 01-Jan-2010 200 Expenditure Expense from Rent 01-Jan-2010 100 Revenue Revenue from Electronics 01-Jan-2011 1000 Revenue Revenue from Vegetables 01-Jan-2011 2000 Revenue Revenue from Food Item 01-Jan-2011 3000 Expenditure Expense from Salary 01-Jan-2011 4000 Expenditure Expense from Rent 01-Jan-2011 3000 Financing Fund from Investor 1 01-Jan-2011 400 Financing Fund From Investor 2 01-Jan-2011 600
I would like to see the report in Tableau as like below with Financing. Please let me know how to do it?
Revenue Revenue from Food Item 100 1000 Revenue from Vegetables 200 2000 Revenue from Electronics 300 3000 Expenditure Expense from Salary 200 4000 Expense from Rent 100 3000 Profit / Loss (Revenue - Expenditure) 300 -1000 Financing Fund from Investor 1 400 Fund from Investor 2 600 Total 1000
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.