6 Replies Latest reply on Nov 23, 2016 10:55 AM by Joshua Milligan

    Non-Additive Totals

    Joseph Bertram

      Hi Everyone,

       

      I have a relatively common (in terms of Financials and Accounting report) requirement around reporting non-additive totals.

       

      In accounting, on a P&L statement, it is common to show Revenue values as Positive and Expense values as positive, however, the total of the two needs to be subtractive (i.e. Total (Profit) = Revenue - Expense).

       

      Is there a way to specify how Tableau rolls up data into Totals?

       

      In case it helps, the data tends to be structured as a dimension Account Class (i.e. Revenue or Expense to keep the example simple) and a fact Transaction Amount.

        • 1. Re: Non-Additive Totals
          Joe Oppelt

          You'll have to have a calc of your own, most likely.

           

          If I understand the way you described your data, this sort of calc will work:

           

          SUM( IF [Account Class] = "Revenue" then [Transaction Amount] END)  -

          SUM( IF [Account Class] = "Expense" then [Transaction Amount] END)

          1 of 1 people found this helpful
          • 2. Re: Non-Additive Totals
            Joseph Bertram

            Sure.  That makes sense.  But how do you define that logic just for the Column Total? 

             

            We want the individual row values to stay as is (i.e. do not flip the sign) but have the Column Total use the calculation that you wrote down.

             

            Do you how to apply that logic just at the total level but not the individual rows?

            • 3. Re: Non-Additive Totals
              Joe Oppelt

              Mock up a sample workbook and show me what you have and describe where you want to go with it.

              • 4. Re: Non-Additive Totals
                Luciano Vasconcelos

                Hi.

                You can use: 

                If Size() = 1 then ....

                 

                 

                • 5. Re: Non-Additive Totals
                  Joe Oppelt

                  You're not going to get Tableau's Grand Total to do it directly.  I'm looking to see what you want your sheet to look like.  There is a way that you can sometimes force your own value into the grand total space, but again, it involves your own calc.  I just don't want to get into the typical cycle of suggesting things and then finding out that there are other factors at play too.

                  • 6. Re: Non-Additive Totals
                    Joshua Milligan

                    Joseph,

                     

                     

                    You can use Joe's suggestion of a custom calc and use some logic determine if you are in a detail or total line.  The key is that in a detail line you'll only have one account class, but the total will be a mix.

                     

                    IF MIN([Account Class]) == MAX([Account Class]) THEN
                    ABS(SUM([Transaction Amount]))
                    ELSE SUM([Transaction Amount])
                    END
                    

                     

                    If the MIN Account Class equals the MAX Account Class, then you are at a detail line and can show the absolute amount.  Otherwise, just take the sum (negatives + positives) and show the result.

                     

                     

                    Alternately, you might also consider simply changing the display format of [Transaction Amount].  Simply right-click the field and select Defaults > Number Format.  Then use a Custom format string to specify the positive and negative have the same format (without a negative indication) -- something like #,##0.00;#,##0.00  (the semi-colon separates the positive and negative formats.

                     

                     

                    Rolling up the values will still be correct (the only issue will be if the total is negative as it will still show as positive).

                     

                     

                     

                    Hope that helps!

                    Joshua

                    2 of 2 people found this helpful