4 Replies Latest reply on Feb 10, 2017 7:26 AM by Jim Dehner

    Show only one Subtotal column

    Kyle Caron

      I am trying to create a subtotal column for current year data and previous year data each split by 2 metrics (inpatient and outpatient).  I want the subtotal to only be shown once, as "subtotal" instead it is being listed under the split as well (inpatient and outpatient); it is calculating the subtotal correctly (the same number under inpatient and outpatient).  Below is a screen shot of an example.  Is there a quick resolution to this?



      Any help would be appreciated.



        • 1. Re: Show only one Subtotal column
          Jim Dehner

          Hi Kyle


          I faced something similar on a project involving budget v actuals across a couple of dimensions


          I was able to create a solution by:

          • Duplicating the input data source (just right click it and pick duplicate)
          • I then renamed them Actual and Budget and did the same on dimensions and measures to keep from getting confused
          • then you go to data >> edit relationships
            • there you will see a primary and secondary source and a table that lists the links -
              • all the fields will be linked
              • just click on the Custom radio button
              • Highlight all the fields and hit Remove
              • then go to ADD and make the connections you need in your viz -
          • Once you've done that you go back to the viz and take recreate the table by selecting the fields you need from the individual data sources
            • I had to supplement with some calculated fields nut it was worth it - my report is updated monthly and now it can be automatic by refreshing the original and the copy of the data source

          Hope that helps

          let me know


          2 of 2 people found this helpful
          • 2. Re: Show only one Subtotal column
            Shinichiro Murakami

            Hi Kyle


            Provided you can use Union (version 9.3 or newer / Data connection allows Union)

            You can create below Viz.



            Edit Datasource and create Union


            // Inpatient and outpatient is in "Type"


            if [Table Name]="Sheet1" then [Type] else "Sub Total" end






            2 of 2 people found this helpful
            • 3. Re: Show only one Subtotal column
              Kyle Caron

              thanks for your help

              • 4. Re: Show only one Subtotal column
                Jim Dehner

                You are welcome

                If you don't mind please mark the original message as This Helped me