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

# Display only one Grand Total column measure

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

Andrew,

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

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

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