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
How can get it done in DAX?
Go to Solution.
The DAX-measure would look like this:
here is how it works:
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
View solution in original post
drop salesman and rate on your tabel visual, in drop down next to rate column, select average as aggregated method.
Proud to be a Datanaut! Appreciate your Kudos 🙂 Feel free to email me with any of your BI needs.
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)
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"
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.
Contract: 120.000,00€ - Rate: 3,50% - Periode: 36 months
Contract: 100.000,00€ - Rate: 2,00% - Periode: 24 months
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%
Hoop this is clear for you...
@KelvinMorel why don't you just refer to your previous post? This explains where the weighted average comes from.
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 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?
Do you need help in Spanish? Check out our new Spanish community section.