Tiered sales commission calculations in Power Query
Use case · By Tahir Nas · Examples run in our simulator · Updated
11 min read
To calculate tiered commission in Power Query, keep the tiers in a table (where each band starts, where it ends and its rate) and write one custom function that turns a sales amount into commission using that table. Group sales by rep and period first, then call the function on each total; whether the rate applies to each slice or to the whole amount is a one-line difference in the function.
Two kinds of tiered commission
Commission plans written as "2% up to 10,000, 4% from 10,000 to 25,000, 6% above 25,000" can mean two different things, and the difference is worth thousands over a year. Read the plan document (or ask whoever owns it) before writing any M.
| Scheme | Rule | Commission on 18,000 sales |
|---|---|---|
| Marginal (stepped, progressive) | Each slice of sales earns the rate of the band it falls in | 10,000 × 2% + 8,000 × 4% = 520 |
| Whole-amount (slab, retroactive) | All sales earn the rate of the highest band reached | 18,000 × 4% = 720 |
Marginal tiers work like income tax bands. Whole-amount tiers are simpler to explain but have a cliff at each threshold: a rep one sale short of the next band earns noticeably less than one who crosses it. Both are common, and Power Query handles both from the same tier table.
The tier table and the commission function
The tier table has three columns: From, To (blank for the top band) and Rate. Each function takes an amount and returns commission. Table.ToRecords turns the tiers into a list of records so the marginal function can loop over the bands, and Microsoft's page on understanding Power Query M functions covers the (amount as number) as number => syntax.
let
Tiers = #table(type table [From = number, To = nullable number, Rate = number],
{{0, 10000, 0.02}, {10000, 25000, 0.04}, {25000, null, 0.06}}),
Sales = #table(type table [Rep = text, Sales = number], {{"Asha", 8000}, {"Ben", 18000}, {"Cara", 31000}}),
Marginal = (amount as number) as number =>
List.Sum(List.Transform(Table.ToRecords(Tiers), (t) =>
List.Max({0, (if t[To] = null then amount else List.Min({amount, t[To]})) - t[From]}) * t[Rate])),
Slab = (amount as number) as number =>
amount * List.Last(Table.SelectRows(Tiers, each [From] <= amount)[Rate]),
WithMarginal = Table.AddColumn(Sales, "Marginal", each Marginal([Sales]), type number),
WithSlab = Table.AddColumn(WithMarginal, "Slab", each Slab([Sales]), type number)
in
WithSlabResult
| Rep | Sales | Marginal | Slab |
|---|---|---|---|
| Asha | 8000 | 160 | 160 |
| Ben | 18000 | 520 | 720 |
| Cara | 31000 | 1160 | 1860 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Asha, at 8,000, stays in the first band, so both schemes pay 160. Ben's 18,000 earns 520 on the marginal scheme and 720 on the slab scheme. Cara's 31,000 earns 1,160 marginal (200 + 600 + 360) and 1,860 slab.
How the marginal function works
For each band, the function works out how much of the amount falls inside it: the smaller of the amount and the band's upper limit, minus the band's lower limit, and never less than zero. For Ben's 18,000:
- Band 0 to 10,000: min(18,000, 10,000) − 0 = 10,000 at 2% = 200
- Band 10,000 to 25,000: min(18,000, 25,000) − 10,000 = 8,000 at 4% = 320
- Band 25,000 and up: 18,000 − 25,000 is negative, so 0
List.Max({0, …}) is the "never less than zero" part. Without it, the top band would subtract commission for anyone below 25,000. List.Sum adds the three band amounts.
How the slab function works
The slab function keeps the bands whose From is at or below the amount, takes the last one's rate, and multiplies the whole amount by it. That relies on the tier table being sorted by From, which is one of the checks later in this post.
The cliff at each threshold
The slab scheme's cliff is easy to show. Here are three amounts either side of the first threshold, rounded to two decimal places:
let
Tiers = #table(type table [From = number, To = nullable number, Rate = number],
{{0, 10000, 0.02}, {10000, 25000, 0.04}, {25000, null, 0.06}}),
Slab = (amount as number) as number =>
amount * List.Last(Table.SelectRows(Tiers, each [From] <= amount)[Rate]),
Marginal = (amount as number) as number =>
List.Sum(List.Transform(Table.ToRecords(Tiers), (t) =>
List.Max({0, (if t[To] = null then amount else List.Min({amount, t[To]})) - t[From]}) * t[Rate])),
Sales = #table(type table [Sales = number], {{9999}, {10000}, {10001}}),
WithSlab = Table.AddColumn(Sales, "Slab", each Number.Round(Slab([Sales]), 2, RoundingMode.AwayFromZero), type number),
WithMarginal = Table.AddColumn(WithSlab, "Marginal", each Number.Round(Marginal([Sales]), 2, RoundingMode.AwayFromZero), type number)
in
WithMarginalResult
| Sales | Slab | Marginal |
|---|---|---|
| 9999 | 199.98 | 199.98 |
| 10000 | 400 | 200 |
| 10001 | 400.04 | 200.04 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
At 9,999 both schemes pay 199.98. At 10,000 the slab scheme jumps to 400 while the marginal scheme pays 200. At 10,001 they are 400.04 and 200.04. One extra unit of sales doubles the slab commission, which gives reps a strong reason to push a deal across a threshold before the period closes. If the numbers in your report look like that, check that the plan really is a slab plan.
Notice the boundary rule too: the slab function uses [From] <= amount, so a total of exactly 10,000 earns the higher rate. If the plan says "above 10,000", change it to <. Plans are often vague about the exact threshold, and that's a question for the plan owner, not something to guess.
Apply the tiers to the period total, not each sale
Commission plans set tiers on a period total: sales per rep per month or per quarter. Sales data arrives as individual lines, so group first and apply the function to each total:
let
Lines = #table(type table [Rep = text, Date = date, Amount = number],
{{"Ben", #date(2026, 9, 3), 6000}, {"Ben", #date(2026, 9, 17), 7000}, {"Ben", #date(2026, 9, 28), 5000},
{"Asha", #date(2026, 9, 10), 8000}, {"Ben", #date(2026, 10, 2), 4000}}),
Tiers = #table(type table [From = number, To = nullable number, Rate = number],
{{0, 10000, 0.02}, {10000, 25000, 0.04}, {25000, null, 0.06}}),
Marginal = (amount as number) as number =>
List.Sum(List.Transform(Table.ToRecords(Tiers), (t) =>
List.Max({0, (if t[To] = null then amount else List.Min({amount, t[To]})) - t[From]}) * t[Rate])),
WithMonth = Table.AddColumn(Lines, "Month", each Date.StartOfMonth([Date]), type date),
PerMonth = Table.Group(WithMonth, {"Rep", "Month"}, {{"Sales", each List.Sum([Amount]), type number}}),
WithCommission = Table.AddColumn(PerMonth, "Commission", each Marginal([Sales]), type number),
Sorted = Table.Sort(WithCommission, {{"Rep", Order.Ascending}, {"Month", Order.Ascending}})
in
SortedResult
| Rep | Month | Sales | Commission |
|---|---|---|---|
| Asha | 2026-09-01 | 8000 | 160 |
| Ben | 2026-09-01 | 18000 | 520 |
| Ben | 2026-10-01 | 4000 | 80 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Ben's three September sales add up to 18,000 and earn 520. His October sale of 4,000 starts the bands again from zero and earns 80, because the tiers reset each month. Asha's September total of 8,000 earns 160.
If you applied the function to each sale instead, Ben's September lines (6,000, 7,000 and 5,000) would each sit inside the first band and earn 120, 140 and 100: 360 in total, not 520. Neither figure is wrong in arithmetic; only one matches the plan. The lesson on Group By with several aggregations covers the grouping step, and Table.Group lists its options.
Two practical points about periods:
- Use the date that the plan uses. Order date, invoice date and payment date can put the same sale in different months. Commission plans usually name one; use that column for
Month. - Credit notes and returns reduce the total. Keep them in the lines as negative amounts so they lower the period total before the tiers apply, unless the plan says otherwise.
Keep the tier table easy to maintain
Whoever maintains the plan shouldn't need to keep From and To in step by hand. Store only From and Rate, and let the query work out each band's upper limit from the next band's start:
let
Tiers = #table(type table [From = number, Rate = number], {{0, 0.02}, {10000, 0.04}, {25000, 0.06}}),
Sorted = Table.Sort(Tiers, {{"From", Order.Ascending}}),
Froms = List.Buffer(Sorted[From]),
Indexed = Table.AddIndexColumn(Sorted, "i", 0, 1),
WithUpper = Table.AddColumn(Indexed, "To", each if [i] + 1 < List.Count(Froms) then Froms{[i] + 1} else null, type nullable number),
Result = Table.RemoveColumns(WithUpper, {"i"})
in
ResultResult
| From | Rate | To |
|---|---|---|
| 0 | 0.02 | 10000 |
| 10000 | 0.04 | 25000 |
| 25000 | 0.06 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is the same three bands with To filled in as 10,000, 25,000 and null. Adding a fourth band is one new row. In a workbook, the tier table can be an Excel table on a settings sheet that the plan owner edits, read the same way as an Excel cell or settings table used as a parameter; the guide to mapping tables instead of nested if explains why a lookup table beats hard-coded thresholds.
If you do keep both columns, check them on every refresh. A gap between bands (one ending at 10,000 and the next starting at 12,000) means sales between those figures earn nothing on the marginal scheme. This check stops the refresh with a clear message instead:
let
Tiers = #table(type table [From = number, To = nullable number, Rate = number],
{{0, 10000, 0.02}, {12000, 25000, 0.04}, {25000, null, 0.06}}),
Rows = Table.ToRecords(Tiers),
Gaps = List.Select({1..List.Count(Rows) - 1}, each Rows{_}[From] <> Rows{_ - 1}[To]),
Checked = if Rows{0}[From] <> 0 or not List.IsEmpty(Gaps)
then error Error.Record("Bad tier table", "Each tier must start where the previous one ends", Text.Combine(List.Transform(Gaps, each "row " & Text.From(_ + 1)), ", "))
else Tiers
in
CheckedResult: an error
Bad tier table: Each tier must start where the previous one ends
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The error's detail names row 2, whose From of 12,000 doesn't match the previous band's To of 10,000. The lesson on validating data and failing loudly covers this pattern in general.
Rounding commission the way finance expects
Commission is money, so it needs rounding to the currency's smallest unit, and the rounding rule matters more than people expect. Number.Round on Microsoft Learn states that ties are broken by rounding to the nearest even number by default (RoundingMode.ToEven, known as banker's rounding). Your payroll or finance system may round halves away from zero instead, and the two rules give different pennies:
let
Amounts = #table(type table [Commission = number], {{0.125}, {0.375}, {0.625}}),
Default = Table.AddColumn(Amounts, "Default", each Number.Round([Commission], 2), type number),
Away = Table.AddColumn(Default, "AwayFromZero", each Number.Round([Commission], 2, RoundingMode.AwayFromZero), type number)
in
AwayResult
| Commission | Default | AwayFromZero |
|---|---|---|
| 0.125 | 0.12 | 0.13 |
| 0.375 | 0.38 | 0.38 |
| 0.625 | 0.62 | 0.63 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
With the default, 0.125 rounds to 0.12 and 0.625 to 0.62 (towards the even digit), while 0.375 rounds to 0.38. With RoundingMode.AwayFromZero, they round to 0.13, 0.38 and 0.63. Across hundreds of reps the difference is small, but a commission report that disagrees with payroll by a penny on some lines will be questioned on every line. Ask which rule payroll uses and match it. When one total is split across several lines instead, rounding each line can miss the total; allocating shared costs across departments shows how to put the difference on one line.
Round once, at the end, on the final commission per rep and period. Rounding each band's amount or each sale first and then adding can drift from the figure payroll calculates. The lesson on rounding numbers shows the other rounding modes, and the Number.Round reference on this site has tested examples.
Practise the steps
The core skills are custom functions, lookups against a band table and rounding. The lesson on writing and using custom functions builds the function step by step. Then try two exercises: Label orders with a price band from a band table uses the same "which band does this amount fall in" logic as the slab scheme, and Write a tax function with an optional rate practises a reusable function with parameters. On Power Query Exercises each answer is checked against hidden data, so an off-by-one at a threshold shows up.
Frequently asked questions
Can different reps have different tier tables?
Yes. Add a Plan column to the tier table and a plan code to each rep, then filter the tiers to the rep's plan inside the function, or pass the filtered tier table as a second parameter.
How do I handle a target-based plan where rates depend on percentage of quota?
Calculate attainment first (sales divided by the rep's quota for the period), then use attainment as the amount in the band lookup, with bands such as 0, 0.8 and 1.0. The commission is then the rate from that band applied to sales, or whatever the plan specifies.
Should commission be calculated in Power Query or in DAX?
If the result is a fixed monthly statement per rep, Power Query is a good fit: the logic is visible step by step and the output is a table you can check against payroll. If users need commission recalculated for any slicer selection in Power BI, a DAX measure is the better home. The guide on Power Query M vs DAX covers that choice, and Transform in SQL, Power Query or DAX? adds the database to it.
Sources
- Table.ToRecords (learn.microsoft.com)
- understanding Power Query M functions (learn.microsoft.com)
- Number.Round on Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
- Combine marketing exports from several ad platforms in Power QueryCombine ad platform exports with different column names, date formats and number styles into one Power Query table, then calculate CTR and CPC correctly.
- Stock on hand from inventory movements in Power QueryTurn a log of receipts, issues, transfers and adjustments into stock on hand, stock at any date, running balances and month-end snapshots in Power Query.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.