powerquery - Multiply multiple columns for each quarter and sum the product - Stack Overflow

admin管理员组

文章数量:1293159

Apologies in advance for what I am sure is a simple question, but I am VERY VERY new to M and I am stuck on how to do this.

I have a data set in which I have 4 columns, eligible earnings, eligible earnings STIP rates, proposed IPM and company score. These columns are already in my query as columns because they pull directly from a online form response workbook living on Sharepoint.

In Excel the formula would look like this:

IFERROR(([@[Eligible Earnings Dollar Amounts.Total Q1 Earnings]]*([@[Eligible Earnings STIP Rates.Q1 STIP Rate]]/100)*([@[Proposed IPM:]]/100)*[@[Company Score]])+([@[Eligible Earnings Dollar Amounts.Total Q2 Earnings]]*([@[Eligible Earnings STIP Rates.Q2 STIP Rate]]/100)*([@[Proposed IPM:]]/100)*[@[Company Score]])+([@[Eligible Earnings Dollar Amounts.Total Q3 Earnings]]*([@[Eligible Earnings STIP Rates.Q3 STIP Rate]]/100)*([@[Proposed IPM:]]/100)*[@[Company Score]])+([@[Eligible Earnings Dollar Amounts.Total Q4 Earnings]]*([@[Eligible Earnings STIP Rates.Q4 STIP Rate]]/100)*([@[Proposed IPM:]]/100)*[@[Company Score]]),"")

Snapshot of data:

Would anyone know what the custom column equivalent m code would be? Ideally what I need it to do is for each employee calculate their bonus for each quarter

(eligible earnings dollar * eligible earnings rate / 100)
    * (proposed ipm / 100) * (company score)

and sum up the values for each total and return the output in just one column like this - desired end result:

I have a feeling that based on my internet research maybe a value.multiply is needed but I am unsure of the right structure. Also if anyone could refer me some good learning sources for M code that would be very appreciated!

Error Message

本文标签： powerqueryMultiply multiple columns for each quarter and sum the productStack Overflow