Creating Avg column based on if and group by

[Shir63]

hello guys,

i have these columns:

Sale order

Discount Amount

Price List Amount

is_Discount_Flag

i need to create = Discount amount / Price list amount when is_discount_flag = 'Y'

and i want it to apply on all the rows within the order

sale order	Price list Amount	Discount Amount	is discount flag	Avg Discount
111	100	50	Y	0.35
111	0	0	N	0.35
111	0	0	N	0.35
111	200	55	Y	0.35
222	150	30	N	0.01666667
222	350	10	Y	0.01666667
222	0	0	N	0.01666667
222	100	0	Y	0.01666667
333	300	15	Y	0.05

i have tried this formula:

sum(total aggr(sum( {<[IS Discount Flag] = {'Y'}>} [Discount Amount])),[Order Number])
/

sum(total([List Price Adjusted] ))

and the formula works, but when there is no order number selected, it presents the total of all orders to all rows.

meaning it takes the total discount / total list price and present it to all my rows

and i need it to stay frozen -  always show the avg discount based on the order, no matter what my selections are

appreciate your help

[lironbaram · Accepted solution]

hi this should work for you 

sum({<[IS Discount Flag] = {'Y'}>} total <[Order Number]> [Discount Amount])
/
sum(total <[Order Number] >([List Price Adjusted] ))

[Shir63]

thank you, that worked!

appreciate it