Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all!
So I'm having a really inoing issue... The sum does not seem to work in pivot tables or in the straight tables, and this is not for all dimensions just some, and I'm out of ideas...
So i simply used the function =sum([field]) and it should work right, but it doesn't. The data is uploaded from an excel spread sheet and the sum is correct when you extract fields to a list box but not in the pivot table. So the sum in the list box is 227556 + 339631 + 215615 = 782 802 HOWEVER.... in the pivot table the sum is 1917180
sum([Invoiced Value EUR])
Any ideas?
Thanks guys!
Hi,
you have multiple records containing the same Invoiced Value:
1917180.1149425 = 3 * 227556.55172414 + 3 * 339631.6091954 + 215615.63218391
so the Sum(Distinct [Invoiced Value]) might work if there are always distinct values per site name / supplier name combination, but can this be guaranteed?
Look into your data model for the reasin of those records occuring multiple times
hope this helps
regards
Marco
Hi,
Try like this
aggr(sum([Invoiced Value EUR]),[Site Name],[Supplier Name])
Regards
ASHFAQ
Can you provide sample input
Hi,
Thank you for your answer unfortunately it did not change the out put... still getting the same numbers.
BR,
Hannah
Hi,
Can you upload sample application.
Regards
ASHFAQ
Hi Hannah,
the number 1917180,11 looks like being the TOTAL of all suppliers regardless of your selection!?!
Need your sample data for further help...
Regards
Burkhard
Hi,
like ashfaq_haseeb wrote but maybe using distinct
sum( aggr(sum( DISTINCT [Invoiced Value EUR]),[Site Name],[Supplier Name]))
Best,
Alessandro Furtado
Hi,
you have multiple records containing the same Invoiced Value:
1917180.1149425 = 3 * 227556.55172414 + 3 * 339631.6091954 + 215615.63218391
so the Sum(Distinct [Invoiced Value]) might work if there are always distinct values per site name / supplier name combination, but can this be guaranteed?
Look into your data model for the reasin of those records occuring multiple times
hope this helps
regards
Marco
Thank you Alessandro! That fixed the issue, do you recommend to do this in all tables?
Best regards,
Hannah
Yes.
Regards
ASHFAQ