I need to do a weighted arithmetic mean something like: (a*weight(a)+b*weight(b) +....) / (weight(a)+weight(b) +...). I am able to do this but the problem are the duplicates. I will explain further:
Product line, Year-Quarter, Subregion, Weight, Target
Alpha, FY17-Q4, Spain, 12, 50
Alpha, FY17-Q4, Portugal, 15, 70
Alpha, FY17-Q4, Norway, 13, 80
Alpha, FY17-Q4, Dennmark, 18, 60
I want to represent the Target of the Region Europe( Iberia(Spain+Portugal) + North Europe(Norway+Dennmark) ), so i need to do: (12*50+15*70+13*80+18*60) / (12 +15+13+18).
To do this i have this expression:
sum(Target * Weight)/sum(Weight)
This is not working, i think that becose of the duplicates. I tried to use Distinct in the load script but it is not working (the scripts is huge and messy, someone made it a long time ago and left that monster as a present).
Is there a way to make the sum but only one value of Target and Weight for each SubRegion??
Thanks for the help,
I can explain further if needed.
Solved! Go to Solution.
How about this?
Sum(TOTAL (Weight * Target)) / Sum(TOTAL Weight)
Sum(TOTAL <Subregion> (Weight * Target)) / Sum(TOTAL <Subregion> Weight)
Either of them is working. I think that the problem is that for each Subregion there are around 200 duplicates of the weight. If i could only use 1 I think it would work. I also tried Sum(DISTINCT (Weight * Target)) / Sum(DISTINCT Weight) but sometimes the weights have the same value so it causes problems.
Expected result from given data?
This: (12*50+15*70+13*80+18*60) / (12 +15+13+18).
As I said the problem is that there are many duplicates of the weights for each subregion (I dont understand where they come from).
Did you give this a shot?
Sum(Aggr(Target * Weight, [Product line], [Year-Quarter], SubRegion))/Sum(Aggr(Weight, [Product line], [Year-Quarter], SubRegion))
Sum(Aggr(Target * Weight, SubRegion))/Sum(Aggr(Weight, SubRegion))
This totally worked!! Thanks!!
I will leave this link just in case someone (like i did) needs further info about Aggr Aggr() function
Thanks toboth of you!