I've searched but haven't found my solution. I need to create a weighted average. I want to weight it based on LinesNr. The measure I want to weight is Rev. I created a simple expression that seems to work at the subtotal level, (2?), but not the line-item level (3?). It is LW_Avg = Sum(Rev) * Sum(LinesNr). Then I created another expression which is LW_Avg / LinesNr.
What is the proper way to get a weighted average at the right subtotal level?
Thanks, but I'm trying to do this in a Pivot Table and it seems to be working at Dimensionality=2, but not 1 or 0. I've been playing around with the formula but still haven't found the answer. Any other suggestions?
I think part of the problem is the field/number I want to be a weighted average is already an "average" number (i.e. Avg. Rev).
I've attached a file with sample data, this time trying to calculate average Margin%.
