I attached a Superstore example and created a % Test calculated field which is
SUM ( [Sales]) * SUM([Profit]) / Total( SUM( [Profit] )).
Notice the grand total of % Test is the same as my [Sales] column, but if you add up the values in % Test, they do not equal $2.2Mill.
I currently trying to figure out this logic for a project at work, so any help would be greatly appreciated!
답변 5개
Hi @Jonathon Lipke
see the attached
you are calculating a weighted average - I use the lod method (the second example) https://jimdehner2.blogspot.com/2020/05/faq-series-weighted-averages.html for this very reason - it totals and subtotals properly
try this
sum({ FIXED [Category]:sum([Sales])*
SUM([Profit])})/sum({ FIXED :sum([Profit])})
it returns this
Jim
