3 Replies Latest reply on Mar 28, 2018 2:25 PM by Justin Maxfield

    Totaling Table Calcs

    Justin Maxfield

      Hi all,

       

      I'm looking for a bit of help getting a calculation to work. A sample workbook is attached. 

       

      The gist of the data is that I start with a few shared columns and based on the Sales Channel field, calculate a subtotal with a different set of formulas (Though house seats and group sales are treated the same).  Those work great and my subtotals are all working as expected since I can filter by each Sales Channel.  The very last step is to just take those subtotals and throw the grand total value on the dashboard.  I've been wrestling with this calc for awhile and got into the weeds.  I just can't figure out a way to get the correct total.  The closest I got was the calc below:

       

      IF ATTR([Sales Channel]) = 'Group Sales' then   [Net Due Venue G] + [CC Lift HG] + [Total Adjustments]

      ELSEIF ATTR([Sales Channel]) = 'House Seats' then   [Net Due Venue G] + [CC Lift HG] + [Total Adjustments]

      ELSEIF ATTR([Sales Channel]) = 'Lottery' then [Net Due Total no Lottery] + SUM([CC Lift]) + [Total Adjustments] END

       

      This is just creating 3 columns with the right values for each sales channels, but they cannot be summed with 'Totals' in the Analytics tab. 

       

      The final number I'm looking for is $43,349.06.  I just need a calc to get that value on a worksheet all by itself.  Then I can just drop it onto the dashboard and call it a day.

       

       

      I've spent a lot of time getting the formatting and formulas working as intended for the subtotals.  So ideally, if possible, I'd like to keep the shape of the data as it is.  Thanks in advance for having a look. 

        • 1. Re: Totaling Table Calcs
          Joe Oppelt

          See attached.

          • 2. Re: Totaling Table Calcs
            Joe Oppelt

            Leaving your channels and other calcs as they are, I have added a WINDOW_SUM to add up the three separate values.  I think you need to keep channels on the sheet, and have your three separate calcs as you have them, but you MIGHT be able to simplify this and just sum up everything in one swoop.  I'm not sure and I don't have the time to dig in.


            So I do a WINDOW_SUM of the V3 calc, and tell it to run along Channels.  That gives me the right value, but because there are three marks (one per channel), we get 3 duplicates of the value.  Go to copy 2.  Here I just moved the V3 calc to details.  Go to copy 3.  Here I added an extra calc called index, and also ran it along channels so we get 1,2,3 in the respective marks.


            On copy 4 I dragged [index] from text to filters and selected only for value = 1.  Now we get a single value.  You can clean up formatting as you see fit.

            1 of 1 people found this helpful
            • 3. Re: Totaling Table Calcs
              Justin Maxfield

              Thanks this did the trick.  I'm glad you didn't need to dig in to the rats nest of calculations and thought of a clever way to just get the sum.