1 Reply Latest reply on Mar 28, 2016 7:48 AM by Dan Sanchez

    Grand total for a running calculation

    Adam Kastan

      Hi,

       

      In the attached workbook on the tab METRO I have 2 running total rows and the Grand Total for each is the running total in the last month. Is there a way to sum up all the running total months? I have tried changing all of the measure values "total using" to Sum but that didn't work.

       

      Current view:

       

      January

      February

      March

      October

      December

      Grand Total

      Decom_Curr_Bill_Cnt

      125

      809

      351

      0

      0

      1,285

      Decom_Curr_Bill_MRC

      $108,641

      $792,330

      $556,228

      $0

      $0

      $1,457,200

      Ckts Still Billing Cumulative Savings

      $108,641

      $900,971

      $1,457,200

      $1,457,200

      $1,457,200

      $1,457,200

      Decom_Stop_Bill_Cnt

      716

      524

      5

      1

      2

      1,248

      Decom_Stop_Bill_MRC_Sum

      $420,171

      $373,538

      $140,733

      $1,805

      $16,400

      $952,647

      Ckts Stopped Billing Cumulative Savings

      $420,171

      $793,709

      $934,442

      $936,247

      $952,647

      $952,647

       

      Desired View:

       

      January

      February

      March

      October

      December

      Grand Total

      Decom_Curr_Bill_Cnt

      125

      809

      351

      0

      0

      1,285

      Decom_Curr_Bill_MRC

      $108,641

      $792,330

      $556,228

      $0

      $0

      $1,457,200

      Ckts Still Billing Cumulative Savings

      $108,641

      $900,971

      $1,457,200

      $1,457,200

      $1,457,200

      $5,381,211

      Decom_Stop_Bill_Cnt

      716

      524

      5

      1

      2

      1,248

      Decom_Stop_Bill_MRC_Sum

      $420,171

      $373,538

      $140,733

      $1,805

      $16,400

      $952,647

      Ckts Stopped Billing Cumulative Savings

      $420,171

      $793,709

      $934,442

      $936,247

      $952,647

      $4,037,218

       

      Thank you,

      Adam

        • 1. Re: Grand total for a running calculation
          Dan Sanchez

          Hi Adam!

           

          I've attached a revised copy of your workbook.  To total up the running_sum rows in the Grand Total column we have a couple options available to us.  The first would be to break up the values into two separate worksheets (one for the months and a second for the Grand Total column) and then combine them on a dashboard.  I've put the necessary calculations int he "Option 1" folder in the attached workbook.  We'll need to have the date field on detail and then for the running total lines we'll use a formula like:

           

          IF FIRST() = 0

               THEN WINDOW_SUM(RUNNING_SUM([Decom_Stop_Bill_MRC_Sum]))

          END

           

          For the non-running total lines we'll use a formula like this:

           

          IF FIRST() = 0

               THEN MIN({ exclude [DECOMMISSION] : [Decom_Curr_Bill_Cnt] })

          END

           

          All of the table calcs will be computed using the Date field.

           

          The second option allows us to keep everything on a single worksheet but the configuration is a bit more complicated.  First off, we'll need to create a duplicate of the Date field and then place the duplicate field onto Detail.  Next we'll be leveraging a trick using the SIZE() and INDEX() functions to force different computations into the separate columns depending on whether they are the month columns or the grand total column.  For the running total line we'll use something like this:

           

          IF SIZE() > 1

              THEN IF INDEX() = 1

                      THEN WINDOW_SUM(RUNNING_SUM(SUM([Decom_Curr_Bill_MRC])))

                  END

              ELSE RUNNING_SUM(SUM([Decom_Curr_Bill_MRC]))

          END

           

          And for the non-running total lines we'll use this:

           

          IF SIZE() > 1

              THEN IF INDEX() = 1

                      THEN WINDOW_SUM([Decom_Curr_Bill_Cnt])

                  END

              ELSE [Decom_Curr_Bill_Cnt]

          END

           

          Lastly, all of the table calcs need to be computed using the duplicate date field.  Hope this helps with your analysis!

          Thanks Adam

          1 of 1 people found this helpful