Totals not working correctly

[jamelmessaoud]

Hi guys,

I have an expression

((Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost])) -

(if(Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate) = 0,sum([FFW FTL Cost]) - sum([Ad-Hoc FTL Cost]),Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate)) +

if(Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate) = 0,sum([FFW Drop Cost]) - sum([Ad-Hoc Drop Cost]),Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate)))) /

(Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost]))

The problem I am facing is that the Total is not calculating correctly.

The calculation should be Total Cost Diff (column) divided by Suppliers Total Cost (column).  Then display this as a percentage.

Within the body of the table the calculation is working perfectly but for the total it is completely wrong! This should be doing

2772.49 / 42574.64 = 6.51%

Can anyone shed any light why this is?

Thanks

J

[sunny_talwar · Accepted solution]

You need to use Sum(Aggr()) on both numerator and denominator expression

Sum(Aggr(NumeratorExpression, YourDimension/s))/Sum(Aggr(DenominatorExpression, YourDimension/s))

[jamelmessaoud]

Sounds horrible!

Can you help me do that with the expression I have? The dimensions in my table are Contractor and [Delivery Full Postcode] if that helps?

Thanks

[jamelmessaoud]

Bingo!

Done it.  Thanks Sunny

[sunny_talwar]

Really?

Sum(Aggr(

((Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost])) -

(if(Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate) = 0,sum([FFW FTL Cost]) - sum([Ad-Hoc FTL Cost]),Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate)) +

if(Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate) = 0,sum([FFW Drop Cost]) - sum([Ad-Hoc Drop Cost]),Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate))))

,Contractor, [Delivery Full Postcode]))

/

Sum(Aggr(

(Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost]))

,Contractor, [Delivery Full Postcode]))

[sunny_talwar]

Lol okay

[jamelmessaoud]

Thanks for your help again.  Don't know what I would do without this community/yourself !