1 Reply Latest reply on Feb 19, 2016 5:39 AM by Jonathan Drummey

# SubTotal and GrandTotal not computing correctly

Hi,

I am running into this issue where the sub total and grand total are not showing the correct sum for a calculated field.

The sub total for region "Amer" region is showing -76,377 where as it should be -77,277.. the incorrect total for some reason is add the USA OTHER and Contra Rev Amer Quota to sub total as well.

The grand totals are incorrect as well. The "SFDC or LB Bookings" grand total is not taking into account "Amer" region.

The "SFDC or LB Bookings minus Quota" grand total is for some reason adding "Amer" region" SFDC or LB Bookings" sub total of 34,623 as a negative taking the grand total to -175,000 where as it should be showing -141,277 (-77,277 -64,000)

Given below is an example:

Region: dimension

Area: dimension

Current Qtr Quota: Measure

SFDC or LB Bookings: Calculated field, using this formula: IF [LB Bookings]> 0 THEN [LB Bookings] ELSE SUM([SFDC Bookings) END

SFDC or LB Bookings minus Quota: Calculated field, using this formula:  [SFDC or LB Bookings]-SUM([Current Qtr Quota])

Correct SFDC or LB Bookings minus Quota: Calculated field, using this formula: IF ATTR([*Area])="USA OTHER" then 0 ELSEIF ATTR([*Area])="Contra Rev AMER" then 0 ELSE [SFDC or LB Bookings minus Quota] END

The reason for creating the field was to disregard the -130 and 1,030 for USA OTHER and Contra Rev AMER as these are place holders quotas and there will never be any SFDC or LB Bookings against them hence I don't want to add these to "SFDC or LB Bookings minus Quota".

I would appreciate your help with fixing these issues.

many thanks

Ali

Jonathan Drummey

Matt Lutton

 Region *Area Current Qtr Quota SFDC or LB  Bookings SFDC or LB  Bookings minus Quota Correct 'SFDC  or LB Bookings minus Quota Amer US1 15,000 4,050 -10,950 -10,950 US2 22,000 7,923 -14,077 -14,077 US3 14,000 2,701 -11,299 -11,299 US4 15,000 4,442 -10,558 -10,558 US5 3,200 730 -2,470 -2,470 US6 11,900 3,948 -7,952 -7,952 US7 250 -250 -250 US8 7,200 3,167 -4,033 -4,033 US9 11,850 4,586 -7,264 -7,264 USA OTHER 130 -130 US10 11,500 3,075 -8,425 -8,425 Contra Rev AMER -1,030 1,030 Sub Total 111,000 34,623 -76,377 -76,377 EMEA EMEA1 29,000 9,000 -20,000 -20,000 EMEA2 25,000 5,000 -20,000 -20,000 EMEA3 31,000 7,000 -24,000 -24,000 Sub Total 85,000 21,000 -64,000 -64,000 GrandTotal 196,000 21,000 -175,000 -175,000