cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rahulvyas
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 PersonProductAmount
Person AHouse 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-100010%
1001-200020%
2001-300030%

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"

 

View solution in original post

danextian
Community Champion
Community Champion

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

View solution in original post

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:
Capture.PNG

 

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.

View solution in original post

3 REPLIES 3
danextian
Community Champion
Community Champion

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

View solution in original post

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"

 

View solution in original post

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:
Capture.PNG

 

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.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors