1 Reply Latest reply on Jun 7, 2018 8:27 AM by Mavis Liu

# sumproduct for weight average

Hi, I'm trying to move a formula from an excel that is used to provide a weight average, I'm trying a calculated field using If but doesn't allow me aggregations.

The table is similar to the following one:

FeatureValue

Speed

4
Noise

3

Speed5
Comfort1

My idea is to have an average like

SUMPRODUCT(SUM (Feature Value) *SAMPLE(Feature))/SUM(SAMPLE(Feature)^2)

In this case (9*2+3*1+1*1)/4^2

Can someone help me with the calculated field?

• ###### 1. Re: sumproduct for weight average

Hi Javier,

sum({FIXED [Feature] : SUM([Feature Value])*SUM([Feature 1])})/

(SQUARE(SUM([Feature 1])))

This is an LOD calculation, where the fixed feature part works out the calculation for each feature, then it adds it all up. Then divides by the overall feature total squared.

More information on LODs here Top 15 Tableau LOD Expressions (Practical Examples) and Understanding Level of Detail (LOD) Expressions with Tableau

I've attached the workbook with this calculation so that you can take a look.

I've had to rename the value Feature as Feature 1 because Feature is already the header name for 'Speed', 'Noise' etc.

Thanks,

Mavis