cancel
Showing results for
Did you mean:
Frequent Visitor

## Split commission calculation

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

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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"``````

Community Champion

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),
#"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
)
``````

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

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
Community Champion

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),
#"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
)
``````

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

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"``````

Anonymous
Not applicable

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.

Announcements