Forum Discussion
Interest Rate -Weighted Average
Hi,
I'm new to POWER BI, would like to calculate "Weighted Average" of salesmen but can't get around this, this sample of my data
| ACM | RATE | PERIODE | WEIGHTED |
| Salesman1 | 2,986 | 72 | |
| Salesman1 | 2,785 | 48 | |
| Salesman2 | 2,682 | 60 | |
| Salesman4 | 2,580 | 36 | |
| Salesman2 | 1,723 | 60 | |
| Salesman2 | 4,230 | 72 | |
| Salesman2 | 1,917 | 72 | |
| Salesman1 | 3,665 | 24 | |
| Salesman1 | 2,668 | 72 | |
| Salesman3 | 1,764 | 84 | |
| Salesman2 | 4,663 | 60 | |
| Salesman2 | 1,663 | 60 |
How can get it done in DAX?
Thx
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/
9 Replies
drop salesman and rate on your tabel visual, in drop down next to rate column, select average as aggregated method.
Found how to do it in MS Excel, highlighted Salesman3 and Salesman4 as check data.
In cell G1 =SUMPRODUCT($B$2:$B$13;$C$2:$C$13;--($A$2:$A$13=F1))/SUMIF($A$2:$C$13;F1;$C$2:$C$13)
A B C F G ACM RATE PERIODE Salesmen WRate Salesman1 2,986 72 Salesman1 2,911 Salesman1 3 48 Salesman2 2,829 Salesman2 3 60 Salesman3 1,764 Salesman4 2,580 36 Salesman4 2,580 Salesman2 1,723 60 Salesman2 4,230 72 Salesman2 1,917 72 Salesman1 3,665 24 Salesman1 2,668 72 Salesman3 1,764 84 Salesman2 4,663 60 Salesman2 1,663 60 So I can achive this but will need to created another table/colomn, this should to be possible in DAX, right?
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,
Try this
=SUMX(Data,Data[Rate]*Data[Periode])/COUNTROWS(Data)
Hi Ashish_Mathur,
ImkeF solution is the sintax I was looking for but maybe yours can help out in the future, how do I need to read/understand that?
Many thx
