Aggregation help

[fallenangel6]

Hi,

I have below two tables.

Invoice Table:

Dept	PO ID	invoiceID	invoiceamt
Sales	A1	IN1	5000
Office	A2	IN2	2000
HR	A3	IN3	1500

invoice Expense table:

invoiceID	%distribution
IN1	20
IN1	10
IN1	30
IN1	10
IN2	100
IN3	100

i want to display the below detail

Dept	PO ID	invoiceID	invoiceamt	invoiceamt modified
Sales	A1	IN1	5000	3500
Office	A2	IN2	2000	2000
HR	A3	IN3	1500	1500

where i used (0.01* percentage distribution * invoiceamount) for each entry of the invoice ID in expense table to get the final modified amount.

how can i get the desired output.

[sunny_talwar · Accepted solution]

May be try this

Dimensions

Dept
PO ID
invoiceID

Expression

Sum(invoiceamt)
Sum(invoiceamt) * Sum(%distribution) * 0.01

[xavieravinson]

Please  find the macro file