Apply % for Column to rows Below

[AllisterB]

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

[Melissa]

Hi @AllisterB,

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

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

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

6. Selected Unpivot Other Columns
   
   
7. Added a Custom Column for the Comission
   
   

.
So what is happening in that Custom Column?

• First it references the dPerc Query
  

  

• Table.SelectRows is used to filter the dPerc Query down to one matching row for Type
  

  

• From that one rowed table, I access the first record
  

  

• From that first record, I reference the field name [Discount] to extract the percentage
  

  

• And multiply that by the [Value] from the current record
  

  

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

[EnterpriseDNA]

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!