3 Replies Latest reply on Mar 6, 2018 9:26 PM by Michael Ye

    Display only one Grand Total column measure

    Andrew Shen

      I have a Sheet built out with MOM % and # of records. For the Grand Total column I want to display only the total number of records summed per a row and remove the MOM Test % column.

      When I hide the total column it hides all the MOM % values which I do not want to remove.

       

      Thanks,

      Drew

       

      What I have

       

      Desired end result:

        • 1. Re: Display only one Grand Total column measure
          Michael Ye

          Andrew,

           

          This might help you.

           

          Make a calculated field: Revised MON Test:

           

          IF FIRST()!=LAST() THEN SUM([MOM Test%])

          END

           

          Use this instead of MOM Test, you will see it in Month column but not in Grand Total.

           

          Hope it helps.

           

          Michael Ye

           

           

          1 of 1 people found this helpful
          • 2. Re: Display only one Grand Total column measure
            Andrew Shen

            I get the following error:

             

            I believe this is because I created the calculated field [MOM Test %] versus a value like you are using: (SUM([Number of Records]) - LOOKUP(SUM([Number of Records]), -1))/LOOKUP(SUM([Number of Records]), -1)

            I added this formula to your WB as [Original MOM % Formula]

             

            I then changed your formula to:

            IF FIRST()!=LAST() THEN ([Original MOM % Formula])

            END

            I did this because it would error out otherwise

             

            I tired to get it to work in the book you loaded, but once I make the change it changed the layout to:

            I'm not sure what I'm missing.

            • 3. Re: Display only one Grand Total column measure
              Michael Ye

              Andrew,

               

              Since you use the following lookup table calculation function:

               

              Original MOM % Formula:

               

              (SUM([Number of Records]) - LOOKUP(SUM([Number of Records]), -1))/LOOKUP(SUM([Number of Records]), -1)

               

              and the calculation:

               

              Corrected MOM Test:

               

              if FIRST()!=LAST() THEN ([Original MOM % Formula])

              END

               

              is computing using Month, the first Month is always Null. The reason is that there is no data in former month, which is 12/2017. Therefore, LOOKUP(SUM([Number of Records]), -1) is Null for 1/2018, so the calculation of "Original MOM % Formula" is also Null for 1/2018.

               

              That is the reason why when Original MOM % Formula is a given number, it is correct. When Original MOM % Formula is a table calculation function, the first month in the table is always null (because you the calculation need you to computing using month).

               

              Michael Ye