4 Replies Latest reply on Mar 2, 2016 3:04 PM by Michael Hatfield

    Grand Total as % of Line item?

    Michael Hatfield



      Just wondering if it is possible to have totals appear as a % of a particular line item (Dimension).  For example:


      I have a field "Group7" that is populating my rows, and beside this a column for MTD Actual ... pretty simple.


      The Grand Total ... adds up both these lines and gives me a result ... that too is pretty simple.


      Here's the tricky bit:


      Random1   (2,344)

      Random2     3,066

      Grand Total:  722


      I am trying to find a way to have a line item added, that would calculate (Grand Total as a % of Random2).  I am certain there's a way to build in Calculated Fields, but I am producing a flat table (yuck) that has a minimum of eight measures (Actual, Budget, Variance $, Variance % for MTD and YTD).


      This would reslt in calculated fields being created for six different dimensions ... meaning potentially ~50 additional calculated fields ... my brain hurts just thinking about it.


      Data is filtered ...

      Group 6 (Parent field)

      Group 7 (row names ... Random1, Random2)


      I can't find anywhere online that this has happened before ... so I am a little concerned for the future of this scoredard.

        • 1. Re: Grand Total as % of Line item?
          Michael Miller

          Hi Michael,


          Could you upload a sample packaged workbook (.twbx)? If the data is confidential, a sample packaged workbook with sample data that mimics the structure of the original workbook would be sufficient. Instructions for creating and sending a packaged workbook can be found at the following link:





          If the data source is a cube, creating a packaged workbook will not be possible. In this case, please upload screenshots illustrating the issue and as much descriptive information as possible.


          Uploading a packaged workbook will help all of us help you!

          • 2. Re: Grand Total as % of Line item?
            Michael Hatfield

            Sorry Michael, data is a cube, and most definitely confidential.


            What I effectively need is a way to calculate the total result of 722 as a percentage of "Random 2".


            Group 6 (shaded red) is the parent grouping within a reporting matrix.  Group 6 = "Actual" (for example), and the Group7 names are direct children of the "Actual" parent.


            I apologise in advance for any vagueries here, It is very difficult to load up the workbook that I am using.  Eventhough I am having difficulty finding the most appropriate answer, I am hopeful that the small screenshot will suffice.


            Thank you for your response!  Hopefully, this will help to be one step closer.

            • 3. Re: Grand Total as % of Line item?
              Michael Miller

              So you want to replace the Grand Total field(722) with the result of 722/3,066 shown as a percentage? While also keeping (2,344) and 3,066 as shown in the current view?

              • 4. Re: Grand Total as % of Line item?
                Michael Hatfield

                Essentially, yes.


                The dashboard that I am building ... is requiring a % Margin calculation ... across differing levels.  This means to present the data as required ... I need to create 70 separate calculated fields.


                If there were a way to have the below seven measures all calculated based on the structure of a table, it would make life much simpler in the design:

                • MTD Actual
                • MTD Budget
                • MTD Variance
                • YTD Actual
                • YTD Budget
                • YTD Variance
                • Fiscal Year Budget


                I have gone along the manual calculated fields route, while it's frustrating ... I've done substantial research over three days to summise that it's perhaps the only way forward for this.  Or the only way that has been done previously.