SumProduct DAX Formula needed

[omusekamp]

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:

Recruiter Commission =

SUMX(Crelate_Export,(([Recruiter YTD Revenue] <= [Total End]) * ([Recruiter YTD Revenue] > [Total Start]) * ([Recruiter YTD Revenue] - [Total Start]) * [Total Rate Percetage])

+ SUMX(Crelate_Export, ((([Recruiter YTD Revenue] > [Total End]) * ([Total End] - [Total Start])) * [Total Rate Percetage])))

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??

[Anonymous]

"DAX that is not formatted correctly is not DAX." - Alberto Ferrari. Please use www.daxformatter.com to format your code in the right way.

[omusekamp]

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!