Forum Discussion
SumProduct DAX Formula needed
Hi!
I need help translating my SUMPRODUCT formula in Excel to DAX. This is a Tiered and Fractionated Sales Commission problem that I have solved in Excel, but have unsuccessfully translated into DAX.
Spread Sheet:
https://drive.google.com/file/d/1dyhb-lRqsktowTvm0vD4fIC_zPE9B5Hn/view?usp=sharing
Here's my attempt at recreating the formula in DAX, but you can see the 15% rate bucket is not correct:
Here's the Excel Code:
=SUMPRODUCT((J3<=$E$17:$E$23)*(J3>$D$17:$D$23)*(J3-$D$17:$D$23)*$F$17:$F$23)+SUMPRODUCT(((J3>$E$17:$E$23)*($E$17:$E$23-$D$17:$D$23))*$F$17:$F$23)
DAX Code Attempt:
Some more information:
1) Commissions Rate Table & Crelate Export Table is joined by the 'Bucket Code' key
2) The 'Recruiter YTD Revenue' is a calculated DAX measure in the 'Crelate Export' Table
3) I had to convert the 'Commissions Table' Columns to SUM Measures to be able to use them in the SUMX formula.
ex. 'End' to 'Total End' -- is this correct??
2 Replies
- AnonymousNot applicable
"DAX that is not formatted correctly is not DAX." - Alberto Ferrari. Please use www.daxformatter.com to format your code in the right way.
- AnonymousNot applicable
Thanks for the reference! And yep, still learning and climbing up the Power BI learning curve, I'll get this code properly formatted.
Appreciate your feeback!