Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.