4 Replies Latest reply on May 8, 2018 10:45 AM by Eric Hammond

    Subtotals aren't right

    Christi Kurihara

      Hey all,

       

      I am using 10.4.6 desktop.  I have subtotals turned on for my data, but they don't seem to be working correctly.  I have 2 data sources; 1 is a live feed server and the other is a static excel worksheet that contains exceptions to what is in the server.  In the below, "Adj. Bookings Fcst" is a field in the excel file and as you can see only some of them have adjusted totals.  So I have added a dimension on the server data source (primary) that says:

       

      _Bookings Forecast:

      if ISNULL( SUM([Exceptions (Quadrant Data)].[Adj. Bookings Forecast]) )

      then SUM( [_Factored TCV] ) else SUM([Exceptions (Quadrant Data)].[Adj. Bookings Forecast]) END

       

      In the above, _Factored TCV is also a calculated field that says [$Bookable]*([%]/100), where [$Bookable] and [%] are original fields on the server data source.

       

      Up until this point, all of the calculations have worked without a hitch.  However, when I apply subtotals to columns, it doesn't add up correctly.  As you can see from the below, it appears to only be totaling the "Adj. Bookings Forecast" (the ones that are not null in the excel file), and not the "_Bookings Fcst".

          

       

      Opportunity_Bookings Fcst_Factored TCVAdj. Bookings Forecast
      Deal 15,000,000.0026,167,091.505,000,000
      Deal 25,000,000.005,000,000.00
      Deal 34,000,000.000.004,000,000
      Deal 4375,000.00375,000.00
      Deal 51,379,500.001,379,500.00
      Deal 655,000,000.0062,500,000.0055,000,000
      Deal 77,500,000.007,500,000.00
      Deal 82,587,500.002,587,500.00
      Grand Total64,000,000.00105,509,091.5064,000,000

       

      Does anyone have any idea why this is happening and how to fix it?  Unfortunately due to the nature of the data, I am unable to share a workbook to show what I am experiencing.