Forum Discussion
Interest Rate -Weighted Average
The DAX-measure would look like this:
WAvg:=SUMX(Table1,Table1[RATE]*Table1[PERIODE])/SUM([PERIODE])
here is how it works:
https://powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/
Hi Kelvin, can you explain better whats you need? What´s the relation between RATE & PERIODE...
Doesn´t clear for me, the measure "Weighted Average"
Hi CCHAVEZ,
As shown in my Excel exercice, I need to calculate the "Interest Rate Weighted Average" by Salesmen, this should be the relation between the interest rate given to customers and the length of the contract.
For instance:
Case 1:
Contract: 120.000,00€ - Rate: 3,50% - Periode: 36 months
Contract: 100.000,00€ - Rate: 2,00% - Periode: 24 months
Case 2:
Contract: 120.000,00€ - Rate: 3,50% - Periode: 6 months
Contract: 100.000,00€ - Rate: 2,00% - Periode: 12 months
In both cases average rate it's 2,75% but this a straight average and doens't take in consideration the length of contracts, with weighted avg case 1 is: 2,9% and case 2 is: 2,5%
Excel formula:
=SUMPRODUCT([Array1];[Array2])/SUM([Array2])
or
=((A1*B1)+(A2*B2))/SUM(B1:B2)
Hoop this is clear for you...
Appreciate help,
KelvinMorel why don't you just refer to your previous post? This explains where the weighted average comes from.
Hi MarcelBeug,
Right, the previews post help me to make it in excel but can't figure out how to do it in Power BI, my apologizes.
