Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
drop salesman and rate on your tabel visual, in drop down next to rate column, select average as aggregated method.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |