4 Replies Latest reply on Nov 19, 2018 1:21 PM by Norbert Maijoor

# Custom Grand Total

Creating a customized grand total is... very hard to understand since it seems all case by case.

I have a table as above. On the grand total, I need to show a sum of everything except for Termination. For example, for June, I need to show the total of 2850+37 = 2887. Could you please help me out? Of course, I still need to show 35 on the table for reference.

• ###### 1. Re: Custom Grand Total

Self union your data. You will get a Dimension as Table Name

Write This Calc

If TableName<>"Your Sheet"  AND HC Type<>"Termination" THEN "Grand Total"

ELSE

HC Type

END

Now use this as First Pill in your Row

Thanks

Deepak

• ###### 2. Re: Custom Grand Total

Byung,

Make a calculation, use it as the measure.

IF FIRST()!=last() THEN SUM([Sales])

ELSE SUM(IF [HC Type] !='Termination' THEN [Sales] END)

END

Hope it helps.

Michael Ye

7 of 7 people found this helpful
• ###### 3. Re: Custom Grand Total

Hi Byung,

You can create a copy of that worksheet.  The original worksheet is going to have your first 4 rows without the grand total.  So after you've copied this sheet, turn off the grand total.

Go to the copied sheet, right-ciick on "Termination" and select exclude.  Then hold ctrl (or command on a Mac), select each of your other three rows above Grand Total and select "Hide".  Now you will have your Grand Total without Terminations included.

On a dashboard, drag your grand total worksheet underneath your original worksheet (you can float it if needed to make it look like one worksheet) and you'll have what you're looking for.

Let me know if you have any questions.

Best,Paul

• ###### 4. Re: Custom Grand Total

Hi Yu,

Find my approach as reference below based in Union

1. Display Value:

if [Table Name]='Sheet1' and ([HC type]='Baseline' or [HC type]='Exist'

or [HC type]='Hire' or [HC type]='Termination') then [Value]

elseif [Table Name]='Sheet1\$' and ( [HC type]='Exist'

or [HC type]='Termination') then [Value] END

2. HC type (group)

3. HC type/HC type (group):

if [Table Name]='Sheet1' then [HC type]

elseif [Table Name]='Sheet1\$' then [HC type (group)] END

Hope it helps,

Regards,

Norbert