Split commission calculation

[rahulvyas]

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

[Anonymous · Accepted solution]

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"

[danextian · Accepted solution]

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
    )

[danextian · Accepted solution]

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
    )

[Anonymous · Accepted solution]

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 · Accepted solution]

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.