Calculated column - show subtotal of filter against every row?

[Anonymous]

Hi all,

DAX newbie and first time poster here - using Powerpivot for Excel.  

I am struggling to achieve a simple task in DAX with calculated columns and would be grateful of some help!  My dataset has rebate agreements with the invoice amounts paid.  A single agreement can have multiple products however, rather annoyingly, when the data comes out of the source system the invoice amount appears the first product line (even though it relates to all the products in the rebate.)  My aim is to re-allocate this invoice amount to each row in the dataset proportionally using the accrual reversal data.  Some sample data showing the goal in the last column;

Rebate No.	Product	Accrual Reversed	Invoice Amount	Calc Col Invoice Amount
123	A	500	3000	500
123	B	800		800
123	C	1700		1700
456	B	2000	7000	1866.67
456	C	5500		5133.33

Note that the Invoice Amount <> Accruals Reversed in all cases as per rebate 456 above

I have tried using

=CALCULATE(SUM(Table[Invoice Amount],Table[Rebate No.]=[Rebate No.])*[Accrual Reversed]/CALCULATE(SUM(Table[Accrual Reversed],Table[Rebate No.]=[Rebate No.])

but this gives me exactly the same result as the Invoice Amount column, so in the example I above I still get 3000 and 7000 against the first row for each rebate. 

Any help most appreciated!  

Mike

[stevedep · Accepted solution]

Perhaps you are looking for something like this:

NewInvoiceAmount = 
var __vTotalAccrual = CALCULATE(SUM('Table'[Accrual]); ALLEXCEPT('Table';'Table'[Rebate]))
var __vRowAccrual = CALCULATE(SUM('Table'[Accrual]))
var __vTotalInvoice = CALCULATE(SUM('Table'[TotalInvoice]); ALLEXCEPT('Table';'Table'[Rebate]))
return
(__vRowAccrual / __vTotalAccrual) * __vTotalInvoice

As seen here:

as seen in this pbi file.

Is this what you are looking for? Please mark as solution if so. Kudo's / thumbs up appreciated.

Kind regards, Steve. 

[Anonymous]

Great thanks Steve,

ALLEXCEPT was the missing piece - thanks for your help

[harshnathani]

Hi @Anonymous ,

Can you share the expected output pls.

Regards,

Harsh Nathani