Apply % for Column to rows Below

The attached workbook has a table that calculates the commission for different branches. The Commission rate sits above the sales data for each type of sale. The Commission for each Branch is calculated in total using sumproduct.

I require the commission for each combination of Branch and Sales type in a table.

I think this involves an unpivot but of which columns and how do I apply the Commission rate to the sales amounts.

Thank You

AllisterBook1.xlsm (10.3 KB)

Using O365

Hi @AllisterB,

There are multipe ways to achieve this, here’s what I did.

  1. Placed your data into Excel tables
    image
  2. Brought them into Power Query via the Data tab, From Range
    image
  3. Added an Index column to the dPerc table
  4. With that in place I could, Unpivot Other Columns
    image
  5. Removed the Index column and Renamed the column headers

==== Next selected the second query in the Query Pane ====

  1. Selected Unpivot Other Columns
  2. Added a Custom Column for the Comission

.
So what is happening in that Custom Column?

I hope this is helpful. Here’s my sample file.
Comission sample.xlsm (154.8 KB)

1 Like

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!