3 Replies Latest reply on Aug 6, 2013 1:57 PM by Jonathan Drummey

    Subtotals - Correct Values


      Jonathan Drummey


      Hi Jonathan,


      How do I get the correct subtotals for Ads column.


      Please help me update the totals to reflect proper values for Ads in the workbook attached.



        • 1. Re: Subtotals - Correct Values
          Jonathan Drummey

          What would "correct" subtotals be? The total of the Ads values?

          • 2. Re: Subtotals - Correct Values

            Yes..Ads is a calculated field.


            The value for "Err" test set will be summation of 1st 5 Ads rows and its total will be displayed below the first five rows.


            Similar to this the next dataset "Hold" the summation of next five rows will be displayed.


            As we see the values are wrong in the report.


            Please help with correct way to sum and display the subtotals.



            • 3. Re: Re: Subtotals - Correct Values
              Jonathan Drummey

              See the attached. This uses an undocumented technique (that would be part of a 4th grand totals post) that I've only used a few times so far to help other forum users. It kind of combines the techniques from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2 to deal with the situation where the measure is a table calculation and then we want the subtotal or grand total to return an aggregate of the table calculation. Here's how it works:


              1. increase the level of detail for the the grand total/subtotal computation so the inner table calculation can work and identify that compute using

              2. build a calculation to test whether we are in the regular row computation or grand total computation so we can return different results and, if that calculation is a table calculation, identify that Compute Using

              3. figure out the aggregation for the grand total/subtotal computation and identify that compute using (in this case, it's the same as step 2)

              4. make all other measures that are displayed in the view table calculations and set their compute usings

              5. go to Analysis->Stack Marks->Off to get rid of the mark stacking.

              6. Use a measure that returns non-Null values in every row to Filter out null values to get rid of any additional rows created by the increased level of detail.


              This is kind of like trying to solve a Rubik's cube in the dark. Given the complexity of the underlying calculation and then the complexity of trying to get the totals the way you want, this is something that I'd be more likely to do in a spreadsheet..