Power QueryExercises

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.

SchemeRuleCommission on 18,000 sales
Marginal (stepped, progressive)Each slice of sales earns the rate of the band it falls in10,000 × 2% + 8,000 × 4% = 520
Whole-amount (slab, retroactive)All sales earn the rate of the highest band reached18,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
    WithSlab

Result

RepSalesMarginalSlab
Asha8000160160
Ben18000520720
Cara3100011601860

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
    WithMarginal

Result

SalesSlabMarginal
9999199.98199.98
10000400200
10001400.04200.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
    Sorted

Result

RepMonthSalesCommission
Asha2026-09-018000160
Ben2026-09-0118000520
Ben2026-10-01400080

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
    Result

Result

FromRateTo
00.0210000
100000.0425000
250000.06null

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
    Checked

Result: 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
    Away

Result

CommissionDefaultAwayFromZero
0.1250.120.13
0.3750.380.38
0.6250.620.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

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

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.