Forum Discussion
Helper IIInterest 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/
Helper IIHi 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,
Community ChampionKelvinMorel why don't you just refer to your previous post? This explains where the weighted average comes from.
- KelvinMorel
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.
- ImkeF
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/