*Guest post by ***David Churchward [Twitter]**

I mentioned in my **LAST POST** that I would return with a variation on that commission calculation. Two months later and very much overdue, here it is! Apologies to those of you who have been waiting on me for this.

I left off with a calculation that applied a commission rate based on a rates table. That rate was applied to the total value.

**But what about a scheme where one rate applies to the first N dollars (or pounds, euros, etc.), then another rate applies for the next M dollars, and so on?** Income taxes are calculated this way, for instance. And being able to solve this sort of thing in Excel very efficiently (using PowerPivot) will be generally quite useful.

In this post, I’ll explain how to do this in a model that I refer to as Base Plus. And while I will discuss this purely in the context of tiered commissions, keep in mind that it applies to taxes and many other things as well.

## Quick Recap

Let’s just recap where we got to. In the **FIRST POST** of this series, I used a DAX measure called **Comm_Rate** to compute a commission rate for each individual and each manager for each calendar month.

Comm_Rate

`=IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1&&COUNTROWS(VALUES(People[Name]))=1`

`&&COUNTROWS(VALUES(Types[Type_Code]))=1,`

`IF([Sales_Value]=BLANK(),BLANK(),`

`CALCULATE(MAX(Rates[Rate]),`

`FILTER(Rates,`

`Rates[From]<=[Sales_Value]`

`&&Rates[To]>=[Sales_Value]`

`&&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

`&&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

`&&Rates[Type]=VALUES(Types[Type_Code])`

`&&Rates[Rate_Group]=”Salesperson”`

` )`

` )`

` ),`

` IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1`

` &&COUNTROWS(VALUES(People[Name]))>1`

` &&COUNTROWS(VALUES(Types[Type_Code]))=1,`

` IF([Sales_Value]=BLANK(),BLANK(),`

` CALCULATE(MAX(Rates[Rate]),`

` FILTER(Rates,`

` Rates[From]<=[Sales_Value]`

` &&Rates[To]>=[Sales_Value]`

` &&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

` &&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

` &&Rates[Type]=VALUES(Types[Type_Code])`

` &&Rates[Rate_Group]=”Manager”`

` )`

` )`

` )`

` )`

` )`

This essentially “picks” a rate from the rates table based on a series of parameters that filter the rate table down to one record.

This rate is then applied to a total sales value using SUMX in a measure called Team_Commission (there’s also a Managers_Commission but it’s a variation on a theme) as per **Post 2**.

Team_Commission

`=SUMX(VALUES(People[Name]),`

`SUMX(VALUES(Dates[MonthEndDate]),[Sales_Value]*[Comm_Rate])`

` )`

## An Example of the Outcome

Let’s consider an example as follows:

- Category = AV (Audio Visual)
- Month = 31 Mar 2012
- Order Value = £117,647

The rate table would therefore be filtered as below:

You’ll notice that the table is filtered down to one record where reporting month end date fits between the **From_Date** and **To_Date**, the value fits between the **From** and **To** value fields and the **Type** is limited to AV. Therefore, a rate of 5% is applied to the Order value of £117,647 to give a commission value of £5,882.35.

However, an alternative approach could look like this:

You’ll notice that the first £40k of orders attracts a rate of 1%, the second £40k gets a rate of 3% and the balance of £37,647 attracts the 5%. The totals are then aggregated to give a very different value.

This gives us a complication. On the face of it, we’re no longer filtering down to one record. I pondered on this for a while. Inevitably, SUMX was going to come into the equation and probably rightly so. It strikes me that this is what SUMX was made for. However, this could be expensive with regard to performance and there’s complexity around computing the value to apply the commission rate to.

## But then it struck me!

While I was pondering the SUMX potential, it struck me that there’s an easier route. Take the previous example above. The value of £117,647 falls between the £80k and £125k in the third red row. All records preceding that row within the same set (ie carrying the same **Type** and **From_Date** and **To_Date**) max out on the commission of that row. That is to say, every value above £80k has a “base value” of the two records above it in the list (ie £40k * 1% and £40k * 3%). Therefore, why wouldn’t I simply add these values together on top of a calculation of row 3?

To do this, I introduced a calculated field in my table called “Base” which is simply a calculated column with the following DAX

`=ROUND(([To]-[From])*[Rate],2)`

**[I added the same calculation to the Excel to show this next to my previous example calculation]**

You’ll notice that the first 2 red rows give the correct answer and we only need to do something different with the row where the value falls between the **From** and **To** values. As it turns out, this is very similar to the approach used in the first 2 posts in this series – anyone might think I had planned it!

My approach would therefore be as follows:

- Calculated the sum of “Base” where the total order value is greater than the “To” value (subject to From_Date, To_Date and Type filters still holding). We’ll call this
**Comm2_Base** - Calculate total order value less “From” where the total order value is between “From” and “To”. We’ll call this
**Comm2_Sales****Value** - Use the
**Comm_Rate**as calculated in the first post to multiply against Comm2_SalesValue

## The Dax

It seems logical to start with **Comm2_Base**:

`=IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1`

` &&COUNTROWS(VALUES(People[Name]))=1`

` &&COUNTROWS(VALUES(Types[Type_Code]))=1,`

` IF([Sales_Value]=BLANK(),BLANK(),`

` CALCULATE(SUM(Rates[Base]),`

` FILTER(Rates,`

` Rates[To]<=[Sales_Value]`

` &&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

` &&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

` &&Rates[Type]=VAUES(Types[Type_Code])`

` &&Rates[Rate_Group]="Salesperson"`

` )`

` )`

),

` IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1`

` &&COUNTROWS(VALUES(People[Name]))>1 `

` &&COUNTROWS(VALUES(Types[Type_Code]))=1,`

` IF([Sales_Value]=BLANK(),BLANK(),`

` CALCULATE(SUM(Rates[Base]),`

` FILTER(Rates,`

` Rates[To]<=[Sales_Value]`

` &&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

` &&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

` &&Rates[Type]=VALUES(Types[Type_Code])`

` &&Rates[Rate_Group]="Manager"`

` )`

` )`

` )`

` )`

`)`

You’ll notice that this is very similar to the Comm_Rate measure from Post 1. There’s two sections, one for a team member and the other for a manager. The defining difference is that a manager is defined as having **COUNTROWS(VALUES(People[Name]))>1**. That is to say that there’s more than one person in the aggregation. Both parts of the calculation also define that the aggregation level only has one month by stating **COUNTROWS(VALUES(Dates[MonthEndDate]))=1** and there’s also only one product type **COUNTROWS(VALUES(Types[Type_Code]))=1**.

Aside from that, it’s a simple **CALCULATE** that is used to sum our new column **Rates[Base] **where the filter condition holds. The key difference in the filter condition from our previous Comm_Rate measure is that it states that **Rates[To]<=[Sales_Value]**. Therefore, we’re going to sum the lower value records.

**Comm2_SalesValue** is also very similar

`=IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1`

` &&COUNTROWS(VALUES(People[Name]))=1`

` &&COUNTROWS(VALUES(Types[Type_Code]))=1,`

` IF([Sales_Value]=BLANK(),BLANK(),`

` CALCULATE([Sales_Value]-MAX(Rates[From]),`

` FILTER(Rates,`

` Rates[From]<=[Sales_Value]`

` &&Rates[To]>=[Sales_Value]`

` &&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

` &&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

` &&Rates[Type]=VALUES(Types[Type_Code])`

` &&Rates[Rate_Group]="Salesperson"`

` )`

` )`

` ),`

` IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1`

` &&COUNTROWS(VALUES(People[Name]))>1`

` &&COUNTROWS(VALUES(Types[Type_Code]))=1,`

` IF([Sales_Value]=BLANK(),BLANK(),`

` CALCULATE([Sales_Value]-MAX(Rates[From]),`

` FILTER(Rates,`

` Rates[From]<=[Sales_Value]`

` &&Rates[To]>=[Sales_Value]`

` &&Rates[From_Date]<=MAX(Dates[MonthEndDate])`

` &&Rates[To_Date]>=MAX(Dates[MonthEndDate])`

` &&Rates[Type]=VALUES(Types[Type_Code])`

` &&Rates[Rate_Group]="Manager"`

` )`

` )`

` )`

` )`

`)`

We’re still using the same approach to filter the **Rates** table down, but this time we need to select the record where the **[Sales_Value]** is between the **From** and **To** values in that table (together with other filter elements holding true). Once we’re on that record, we subtract the “**From**” value from the **[Sales_Value]** to calculate the value that the commission rate should apply to.

With **Comm2_Base **and **Comm2_SalesValue** in place, together with **Comm_Rate** from Post 1, we’re in a position to construct our final measure that we’ll call **Commission2**.

`=IF(COUNTROWS(VALUES(People[Manager]))=1,`

` SUMX(VALUES(People[Manager]),`

` SUMX(VALUES(Dates[MonthEndDate]),`

` [Comm2_Base]+([Comm2_SalesValue]*[Comm_Rate])`

` )`

` ),`

` SUMX(VALUES(People[Name]),`

` SUMX(VALUES(Dates[MonthEndDate]),`

` [Comm2_Base]+([Comm2_SalesValue]*[Comm_Rate])`

` )`

` )`

` )`

This measure first determines whether the aggregation level is a manager level with **COUNTROWS(VALUES(People[Manager]))=1**. Where this is true, **SUMX** iterates over the **Dates[MonthEndDate]** values that are in context, delivering a result of **[Comm2_Base]+([Comm2_SalesValue]*[Comm_Rate])** for each **MonthEndDate** and then iterates over every **People[Manager]** in context to apply the results.

Where the aggregation level ISN’T a manager, the same formula executes but this time for each **People[Name]** record in context.

By executing the “Manager” level as the true element of the first **IF**, the Grand Total picks up the formula relating to the **People[Name]**. Therefore, our total gives the result of all team member commissions.

And finally, **YOU CAN DOWNLOAD THE FILE HERE.** Note – this is a PowerPivot V2 file so it won’t work in V1. Sorry if you’re still using V1.

Get in touch with a P3 team member