CODEWITHSUNDEEP
splitrangedaxlookup
KShiromani
KShiromani

Reputation: 1

Split value based on a lookup Range

Need help to add a calculated column for sales commission in each row of the Sales table. The commission % is determined by the total YTD revenue for the sales person for which the lookup range is in Target table.

Sales Table

Targets Table

Example - For Person1 18-Dec-2024 - YTD invoice is 500, so commission @ 5% 13-Jan-2025 - YTD invoice is 2000 (Dec - 500+Jan - 1500), first 500 already paid in Dec, remaining 500 @5% and 1000 @ 6%. 24-Feb-2025 - YTD invoice is 4000 (Dec 500 + Jan - 1500 + Feb 2000) first 2000 already paid, remaining 1500 @ 6% and 500 @ 7% Total = 235

I an unable to break the commission into ranges to multiply with commission % within each range. I want to calculate commission % for each row item based on the YTD revenue for the sales person.

Upvotes: 0

Views: 20

Answers (0)

Related Questions