Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Split commission calculation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Split commission calculation

03-02-2020
03:40 PM

I am fairly new in power BI. I have a sales data set for Salesperson and I have to calculate commission based on the revenue sales team generate. Some sales are done together by 2 salespeople which get split among themselves 50-50 and get added to their final monthly sales used to calculate commission.

I have figured the formula using Sumifs and count if in Excel. Can anyone help how to calculate splits and in power bi?

Data set example

Sales Person | Product | Amount |

Person A | House 1 | $1000.00 |

Person B | House 2 | $1500.00 |

A&B | House 3 | $2000.00 |

Total sales for each salesperson : A: 2000.00 and B: 2500.00

Commission Calculator

0-1000 | 10% |

1001-2000 | 20% |

2001-3000 | 30% |

Salesperson total commission paid : A: $400.00 B: $750.00

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:30 PM

The right solution will require a bit more background to really advise but I'd recommend have a "Person" table and a "Sales" table.

During your import, or if you can solve in the source data, i'd look to create your Sales data such that you have 1 person per row. If a Sale has a split commission, we'd want to split that data into multiple rows (one for each person) with the division of the value as you mentioned.

If your "Sales Person" field is consistant and splits always contain the & symbol, you could set up an algorithm as part of the import to handle this split and create the extra rows. I'd likely achieve this using a "Split column by delimiter" and then add a new column for the divided amount, then unpivot the data.

A quick example based on your data might be:

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lHyyC8tTlUwBLIMDQwMlGJ14LJOcFkjkKwpqqyjQkypgYGRmQKGamMgywhsViwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Product = _t, Amount = _t]),
#"Set Field Types" = Table.TransformColumnTypes(Source,{{"Sales Person", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Set Field Types", "Sales Person", Splitter.SplitTextByEachDelimiter({"&"}, QuoteStyle.Csv, false), {"Sales Person.1", "Sales Person.2"}),
#"Added CommissionAmount" = Table.AddColumn(#"Split Column by Delimiter", "CommissionAmount", each if [Sales Person.2] = null then [Amount] else [Amount] / 2, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added CommissionAmount", {"Product", "Amount", "CommissionAmount"}, "Attribute", "Value"),
#"Removed Attribute" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Rename Value to Person" = Table.RenameColumns(#"Removed Attribute",{{"Value", "Person"}})
in
#"Rename Value to Person"
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:38 PM

Hi @rahulvyas

Assuming that "&" in person is a constant, I would split Sales Person into several rows first such that there is only one sales person per row and then divide the sales amount by the number of sales persons that share in it. I would then either calculate the commission in Power Query or in DAX.

The code below splits Sales Person

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lHyyC8tTlUwPLQAyFYxNDAw0DMwUIrVgatxAstAVBlBVZkiVDnGlBoYGJkhqzIGqTGCmRQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Product = _t, Amount = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"$","",Replacer.ReplaceText,{"Amount"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Sales Person", type text}, {"Product", type text}, {"Amount", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sales Person Split", each let split = Text.Split([Sales Person], "&")
in if List.Count(split)>1 then List.Transform(split, each "Person " & _) else split),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Amount Split", each Value.Divide([Amount], List.Count([Sales Person Split])), type number),
#"Expanded Sales Person Split" = Table.ExpandListColumn(#"Added Custom1", "Sales Person Split"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Sales Person Split",{{"Sales Person Split", type text}})
in
#"Changed Type1"
```

Below DAX computes for the commission.

```
Commission =
VAR SalesAmount = Sales[Amount Split]
RETURN
SWITCH (
TRUE (),
SalesAmount >= 2001, 0.3 * SalesAmount,
SalesAmount >= 1001, 0.2 * SalesAmount,
SalesAmount >= 0, 0.1 * SalesAmount
)
```

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

www.linkedin.com/in/danebelarminocpa

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

www.linkedin.com/in/danebelarminocpa

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:38 PM

To further on this solution, i've also created a commissions rates table:

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI0MABRBnqGSrE60SCuIZBrBBM1AosaQUSNYaLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Commission Low" = _t, #"Commision High" = _t, Commission = _t]),
#"Set Field Types" = Table.TransformColumnTypes(Source,{{"Commission Low", Int64.Type}, {"Commision High", Int64.Type}, {"Commission", Percentage.Type}})
in
#"Set Field Types"
```

Bringing this into the model, i haven't needed to create a relationship between this and the Sales table.

Now in the Sales table i use DAX to create these two columns:

```
Commission Percent = var amount = [Amount]
var output = CALCULATE(
SUM('Commission Rates'[Commission]),
'Commission Rates'[Commission Low] <= amount,
'Commission Rates'[Commision High] >= amount
)
RETURN
output
```

`CommissionPayable = [CommissionAmount] * [Commission Percent]`

This creates a table like this:

**NOTE**

See the extra space before "Person B" on row 4? This is a left over from my code to split those columns. We'll need to go back into those applied steps and put in a "Trim" step. That will clean that mistake up.

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:38 PM

Hi @rahulvyas

Assuming that "&" in person is a constant, I would split Sales Person into several rows first such that there is only one sales person per row and then divide the sales amount by the number of sales persons that share in it. I would then either calculate the commission in Power Query or in DAX.

The code below splits Sales Person

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lHyyC8tTlUwPLQAyFYxNDAw0DMwUIrVgatxAstAVBlBVZkiVDnGlBoYGJkhqzIGqTGCmRQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Product = _t, Amount = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"$","",Replacer.ReplaceText,{"Amount"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Sales Person", type text}, {"Product", type text}, {"Amount", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sales Person Split", each let split = Text.Split([Sales Person], "&")
in if List.Count(split)>1 then List.Transform(split, each "Person " & _) else split),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Amount Split", each Value.Divide([Amount], List.Count([Sales Person Split])), type number),
#"Expanded Sales Person Split" = Table.ExpandListColumn(#"Added Custom1", "Sales Person Split"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Sales Person Split",{{"Sales Person Split", type text}})
in
#"Changed Type1"
```

Below DAX computes for the commission.

```
Commission =
VAR SalesAmount = Sales[Amount Split]
RETURN
SWITCH (
TRUE (),
SalesAmount >= 2001, 0.3 * SalesAmount,
SalesAmount >= 1001, 0.2 * SalesAmount,
SalesAmount >= 0, 0.1 * SalesAmount
)
```

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

www.linkedin.com/in/danebelarminocpa

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

www.linkedin.com/in/danebelarminocpa

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:30 PM

The right solution will require a bit more background to really advise but I'd recommend have a "Person" table and a "Sales" table.

During your import, or if you can solve in the source data, i'd look to create your Sales data such that you have 1 person per row. If a Sale has a split commission, we'd want to split that data into multiple rows (one for each person) with the division of the value as you mentioned.

If your "Sales Person" field is consistant and splits always contain the & symbol, you could set up an algorithm as part of the import to handle this split and create the extra rows. I'd likely achieve this using a "Split column by delimiter" and then add a new column for the divided amount, then unpivot the data.

A quick example based on your data might be:

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lHyyC8tTlUwBLIMDQwMlGJ14LJOcFkjkKwpqqyjQkypgYGRmQKGamMgywhsViwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Product = _t, Amount = _t]),
#"Set Field Types" = Table.TransformColumnTypes(Source,{{"Sales Person", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Set Field Types", "Sales Person", Splitter.SplitTextByEachDelimiter({"&"}, QuoteStyle.Csv, false), {"Sales Person.1", "Sales Person.2"}),
#"Added CommissionAmount" = Table.AddColumn(#"Split Column by Delimiter", "CommissionAmount", each if [Sales Person.2] = null then [Amount] else [Amount] / 2, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added CommissionAmount", {"Product", "Amount", "CommissionAmount"}, "Attribute", "Value"),
#"Removed Attribute" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Rename Value to Person" = Table.RenameColumns(#"Removed Attribute",{{"Value", "Person"}})
in
#"Rename Value to Person"
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2020
04:38 PM

To further on this solution, i've also created a commissions rates table:

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI0MABRBnqGSrE60SCuIZBrBBM1AosaQUSNYaLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Commission Low" = _t, #"Commision High" = _t, Commission = _t]),
#"Set Field Types" = Table.TransformColumnTypes(Source,{{"Commission Low", Int64.Type}, {"Commision High", Int64.Type}, {"Commission", Percentage.Type}})
in
#"Set Field Types"
```

Bringing this into the model, i haven't needed to create a relationship between this and the Sales table.

Now in the Sales table i use DAX to create these two columns:

```
Commission Percent = var amount = [Amount]
var output = CALCULATE(
SUM('Commission Rates'[Commission]),
'Commission Rates'[Commission Low] <= amount,
'Commission Rates'[Commision High] >= amount
)
RETURN
output
```

`CommissionPayable = [CommissionAmount] * [Commission Percent]`

This creates a table like this:

**NOTE**

See the extra space before "Person B" on row 4? This is a left over from my code to split those columns. We'll need to go back into those applied steps and put in a "Trim" step. That will clean that mistake up.