3 Replies Latest reply on Mar 21, 2018 11:15 AM by Daniel Lawrence

    Creating a grand total different from visual representation of numbers

    Daniel Lawrence

      Hi all,

       

         I have what appears to be a fairly easy problem to solve but I cannot seem to find my way around it.  I have accounting data displayed in a manually created hierarchy (Groups Gross Profit, Operating Expenses, Other Income) with three levels of sub-groups which at the base level is aggregating over journal entries.  I want to sum over base-levels to get a column wise sum by the formula of Gross Profit - Operating Exenses + Other Income with the caveat that the values that show up in the column wise entries for Operating Expenses are all positive (rather than negative which would just allow me to sum normally over the column) as per standard accounting practices.  This is what it currently looks like with an incorrect Total or Net Income(Loss):

       

      With negative values for Operating Expenses, I can reach the correct Total or Net Income(Loss) but is not suitable for display.

      Does any one know how to fix this?

       

      Thanks,

      Daniel

        • 1. Re: Creating a grand total different from visual representation of numbers
          Zhouyi Zhang

          Hi, Daniel

           

          If use negative value can get correct grand total, you probably can use custom format for negative value by removing '-' from the format as shown below for display purpose.

           

           

          Hope this helps

           

          ZZ

          • 2. Re: Creating a grand total different from visual representation of numbers
            Daniel Lawrence

            ZZ, 

             

              Thank you pointing this option out but I do not believe it will help as it requires a blanket formatting change for the entire Profit column (in your example).  My scenario is one that allows negatives in the column (and in group 2) but the normal values of group 2 will be positive and I want to have a formula for the total of (All values in the column in Group 1) - (All values in the column in Group 2) + (All values in the column in Group 3). 

             

            The problem with the blanket formatting change is that the normal values of group 3 are generally negative so I do not want to change the formatting for those.

             

            Is there no way to write a basic formula for a total without changing the input that go into that formula?

            • 3. Re: Creating a grand total different from visual representation of numbers
              Daniel Lawrence

              Following up on this, here is a Tableau example that utilizes what I am saying:  https://www.tableau.com/solutions/workbook/cfos-overview-business

               

              The income statement has all positive values listed but if you look closesly Net Sales = Gross Sales - Discounts;  Gross Profits = Net Sales - Cogs;

              Operating Income = Gross Profits - (each value of Operating Expense) OR Operating Income = Gross Profits - (the sum of all values in Operating Expense) etc....

               

              This appears to be a formula rather than just a grand total summed over the column.  In this case, ZZ's suggestion from above could be in play where all negative values are kept as is for mathematical purposes and then the values are summed over the column to reach the correct totals and then all negatives are removed for display purposes.  I am not sure which is the case.

               

              My purposes are ever so slightly different as a negative value appearing in the entries has informative value outside of just how it impacts the total.

              Ideally, I want to be able to put a formula in for the total where (from my original screenshot):

              (sum of all values in Gross Profits, which appear as positives)

              - (sum of all values in Operating Expenses, which generally should appear as positives)

              + (sum of all values in Other Income (Expense), which can appear as negative or positive)

               

              ZZ's suggestion would solve the display problem for Operating Expenses but would create a new problem for Other Income (Expenses) section by hiding informative negative values. 

               

              Hopefully that clarified the situation a bit more and someone has insight into this.