Totals in columns

[hildacgg]

I have a forecast table with budget for each quarter, and actual sales for each quarter, but when calculate my actual sales it is also accumulating the sales for part numbers that are not in my budget. In order to get the right total I have to check 'Sum of Rows' instead of 'expression total' but when I calculate the percentage = actual / budget * 100, my totals at the top is wrong...how can I resolve this? please help.

[settu_periasamy]

Hi,

Can you post the sample and expected output?

[hildacgg]

My total budget is : 50,000 total mode 'Expression Total'

my total sales is : 35,250  with  'sum of rows' total mode

and is 42,300 with 'Expression Total' total mode

the total VAR% shows 84.6% but it should be

70,5% because the correct total sales is 35,250

[settu_periasamy]

what about your expression? have you tried like

Sum(TOTAL ActualSales)/Sum(Budget)

[sunny_talwar]

May be try this:

Sum(Aggr(YourExpression4TotalSales, YourTableDimensions)/YourExpression4Budget

[hildacgg]

Thank you for your answer, still it is not working

I try using Aggr:

=Sum(Aggr(Sum({<[Cost Type]=,[Calendar Week]=,[Fiscal Period]=,[Fiscal Year]={2015},[Fiscal Quarter]={'Q1'}>}[Invoice Quantity] * [Cost Conversion]),[Part Number]))

But when I add the condition to remove part numbers with sales but no budget I get zeros for all records:

=Sum(Aggr(if(([Total (LBS)]>0),Sum({<[Cost Type]=,[Calendar Week]=,[Fiscal Period]=,[Fiscal Year]={2015},[Fiscal Quarter]={'Q1'}>}[Invoice Quantity] * [Cost Conversion]),0),[Part Number]))