Commission Split Calculation PowerBI/ Excel/Python/Tableau

Question

I need to create a measure from the pictured exported data, where sometimes the Total will be e.g. $1000 from one representative (i.e. Kate Pearson) and at other times it will be split between two people (Kate and Randal, each have $500) or even three.

It is somewhat similar to argmax in Python, but I cannot figure out what calculation to use to extract the data in this manner.

What I want is for each opportunity, a list of totals by either 1 or more people, and if more than one, to list the total along with the person who was responsible.

If anyone is happy to share their logic in python code or even Excel or PowerQuery, that will be a great starting point.

Example of output I want to achieve:

Totals:

Randal total: 1750
Kate Total: 2100
Kevin Total: 1150

Answer 1

You could do this in Power Query (Home=>Transform)

I assume your above table name is Sales Table. If not make the change in the Source line of the below code.

The algorithm is outlined in the code comments. But also explore the applied steps to see what each step is doing:

let
    Source = #"Sales Table",

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(Source,{"OpportunityID", "Total Sales"}),

//Unpivot all the columns and remove the Attribute column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),

//We now have pairs of Rep and Split in a single column
// So add an index to group the pairs
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

//Aggregate by creating a two column table
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Integer-Division"}, {
        {"all", each [rep=[Value]{0}, Split=[Value]{1}] }}),
    #"Expanded all" = Table.ExpandRecordColumn(#"Grouped Rows", "all", {"rep", "Split"}, {"rep", "Split"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded all",{"Integer-Division"}),

//Remove the "NA" reps
//then group by Rep and aggregate by Sum
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns3", each ([rep] <> "NA")),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"rep"}, {{"Rep Total", each List.Sum([Split]), type number}})
in
    #"Grouped Rows1"