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:
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.
|Region||*Area||Current Qtr Quota||SFDC or LB|
|SFDC or LB|
Bookings minus Quota
or LB Bookings minus Quota
|Contra Rev AMER||-1,030||1,030|