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.

       

      Thanks.

        • 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

          Jim

          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"

            [Header]

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

             

             

             

            Thanks,

            Shin

            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

                 

                Thanks

                Jim