Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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??
"DAX that is not formatted correctly is not DAX." - Alberto Ferrari. Please use www.daxformatter.com to format your code in the right way.
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!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |