Get the group by value in a calculated column

[Anonymous]

Hello,

I got a simple task that I can't seem to get my head around.

I have a table with following data:

Order no	Part No	Component part	QtyOnOrder	DateDue
1000	123	234	100	19.3.2020
1001	123	234	200	20.3.2020
1001	123	567	200	20.3.2020

And I have another table with just the Part No. In that table I want to get the Qty per group, so in this case

123 = 100 + 200

I tried using SUMMARIZE, but I already have +30 tables in my dataset and don't want to clutter it any extra. 

The tables are related. And if there's a better way to get the qty of the distinct order no to the same product, please suggest so.

So order no 1001 will create 200 pcs 123, but use 234 and 567 to be created. And I need to be able to filter on the date (which is related to the date table).

Thanks in advance!

EDIT: because I need the date field, the best idea is probably to go with a measure that can calculate the average of qty on order based on the group function of order no. Is that possible?

[amitchandak]

@Anonymous , New column in  your part no table

New column = Sumx(filter(Table, Table[Part No] = part[Part no]),Table[QtyOnOrder])

[Anonymous]

Would work. But the column QtyOnOrder contains "duplicates" which is based on the order no, so the result with your expression would lead to 500 (instead of the wanted 300).

Thanks!

[Anonymous]

Found this:

Measure:

TotalOrderAmount = SUMX(DISTINCT(Table1[Order no]),CALCULATE(AVERAGE(Table1[qty])))