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
          Deepak Rai

          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"


          HC Type



          Now use this as First Pill in your Row



          • 2. Re: Custom Grand Total
            Michael Ye



            Make a calculation, use it as the measure.


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

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



            Hope it helps.


            Michael Ye


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

              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.


              • 4. Re: Custom Grand Total
                Norbert Maijoor

                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,