1 Reply Latest reply: Feb 19, 2015 11:55 AM by Henric Cronström

# Weighted Average Pivot Table

Hi everyone,

I have a pivot table with 5 dimensions, and partial sums activated for all of the dimensions. I would like to add an expression for weighted average of interest rates for the loans in my table (based on the outstanding balance). It would look something like this:

sum((outstanding_balance/sum(total outstanding_balance))*interest_rate)

Then, when you add the individual values of this expression together for each loan you should get the weighted average of interest rate for all the loans.

I added this expression to my pivot table, but the confusion lies in the partial sums of the dimensions. If I'm not mistaking, the only total on my chart that would give me an accurate reading of weighted interest rate is the total that adds the entire column. Is there a way that I can get an accurate weighted average for the partial sums of the dimensions as well?

Thanks

• ###### Re: Weighted Average Pivot Table

I think you will get the right numbers also in the partial sums if you use

Sum(outstanding_balance*interest_rate) / Sum(outstanding_balance)

This will calculate the average interest rate, but weighted by the balances.

HIC