Forum Discussion
KelvinMorel
Helper II
Helper IIInterest 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 ...
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/
KelvinMorelHelper II
Helper IIFound 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?