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

    Profit Loss Calculation

    mohamedferozkhan.m

      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
          John Sobczak

          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
            Andrew Watson

            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
              mohamedferozkhan.m

              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
                Andrew Watson

                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.