Orders with possible item

[madhuqliklondon]

Hi all,

I am trying to get list all products and order value when a particular item exits in that order

Ex:

Order.no	Item.no	Amount
1	22	5
1	23	6
1	24	7
2	22	5
2	22	5
2	23	6
2	29	9
2	25	3
3	22	5
3	23	6
3	23	6
3	24	7
3	29	9

Result: show only order contains item.no=24 and order totals

Order.no	Item.no	Amount
1	22	18
1	23
1	24
3	22	33
3	23
3	23
3	24
3	29

I am using below expression to get totals

Sum({<[Item.no]={'24'}>}Aggr(Sum(TOTAL <[ Order.no]> {<[ Order.no = P({<[Item.no]={'24' }>}[ Order.no]),[Item.no]>}[Amount]),[ Order.no], [Item.no])) 

I want to get the all the items in orders. Is that possible??

[sunny_talwar]

All items in orders? Not sure I understand? Can you elaborate?

[effinty2112]

Hi Madhu,

Does this give you what you need?

Order.no	sum({$<Order.no = P({<Item.no = {24}>})>}Amount)
1	18
3	33

Regards

Andrew

[sunny_talwar · Accepted solution]

May be this

Sum(TOTAL <[Order.no]> {<[Order.no] = P({<[Item.no]={'24' }>}[Order.no]),[Item.no]>}[Amount])

[Kushal_Chawda]

abhi me ye expression dalne hi wala tha.. but tera dekh k rook gaya

[effinty2112]

Hi Madhu,

A pivot table maybe gives a nice presentation.

Order.no	Item.no	sum({$<Order.no = P({<Item.no = {24}>})>}Amount)
1	22	5
	23	6
	24	7
	Total	18
3	22	5
	23	12
	24	7
	29	9
	Total	33

[madhuqliklondon]

Thank you Andrew.

[madhuqliklondon]

Thanks bhai.