4 Replies Latest reply on Jan 11, 2019 3:05 PM by Shawn Suarez

# % to Goal Grand Total Calculation

Hello,

I have an issue on calculating % to Goal from a grand total perspective. I have recreated my issue using the superstore data and attached the workbook.

Essentially, I have 4 regions and they all have hard coded Sales Amounts. I created the "Region Goal" measure as basically an IF [Region] = selected region THEN goal amount. What I want to do is create a calculated field that is % to Goal where it would be the regions sales amounts / Region Goal Amount. I have done that in the workbook and the calculation works for each region, however, once I add a grand total to the mix it throws off the grand total % to goal.

Can someone show me how to get this to reconcile correctly?

Thanks!

• ###### 1. Re: % to Goal Grand Total Calculation

I Shawn,

I'm unable to open the file you uploaded however, i don't think the 'Show column grand totals' works on IF statements.

can you post a screen shot or replicate the result you want with a dummy data set in excel?

• ###### 2. Re: % to Goal Grand Total Calculation

Hi Albert,

Please see below. The % to Goal calc works across the regions but is incorrect at the grand total level. Thanks!

• ###### 3. Re: % to Goal Grand Total Calculation

Did you just want the % to Goal to Sum as 142.40%?

If so go to Analysis > Totals > Total All Using > SUM

• ###### 4. Re: % to Goal Grand Total Calculation

No, sorry I'm looking for the percent to goal in the Grand total to be \$2,297,201/\$14,500,000 or 15.8%