
1. Re: Display only one Grand Total column measure
Michael Ye Mar 6, 2018 8:41 AM (in response to Andrew Shen)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

nu.twbx 9.7 KB


2. Re: Display only one Grand Total column measure
Andrew Shen Mar 6, 2018 9:57 AM (in response to Michael Ye)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.

nu.twbx 8.9 KB


3. Re: Display only one Grand Total column measure
Michael Ye Mar 6, 2018 9:26 PM (in response to Andrew Shen)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