Issue with sum and averages between tables

[andreazambon]

Hi guys. 

Suppose to have those tables:

ProductionTable
Production Date	ProdOrder	Line	kg
23/06/2024	123	1	100
23/06/2024	123	1	100
23/06/2024	123	1	100
23/06/2024	123	1	100
23/06/2024	456	1	150
24/06/2024	456	1	150
24/06/2024	456	1	150
24/06/2024	456	1	150
22/06/2024	789	2	200
22/06/2024	789	2	200
22/06/2024	789	2	200

OrderTable
ProdOrder	kg	Date
123	400	22/06/2024
456	600	23/06/2024
789	600	22/06/2024

DateTable
DateTable	Month
01/06/2024	6
02/06/2024	6
03/06/2024	6
04/06/2024	6
05/06/2024	6
06/06/2024	6
07/06/2024	6
08/06/2024	6
09/06/2024	6
10/06/2024	6
11/06/2024	6
12/06/2024	6
13/06/2024	6
14/06/2024	6
15/06/2024	6
16/06/2024	6
17/06/2024	6
18/06/2024	6
19/06/2024	6
20/06/2024	6
21/06/2024	6
22/06/2024	6
23/06/2024	6
24/06/2024	6
25/06/2024	6
26/06/2024	6
27/06/2024	6

The tables are connected following these relationships:

-ProductionTable[Production Date]-->DateTable[Date]

-ProductionTable[ProdOrder]-->OrderTable[ProdOrder]

-OrderTable[date]-->DateTable[Date] (inactive)

The goal is to build a measure that show me the average of the order's quantity taken by OrderTable, with the possibility to filter in a filter view the Line and the Period. My difficulty is that I don't know how to link the column ProductionTable[Line] with OrderTable[ProdOrder].

The questions could be:

"what is the average dimensions of orders?"

The answer is: in 22/06 =500kg, in 23/06 = 600kg.

"What is the average dimensions of Orders produced by Line 1?

The answer is 500kg.

As you can see from the tables, the ProductionDate of an order could be different from the Date in the OrderTable. It doesn't matter: the Date in OrderTable is the one i'm interested to check.

I need to do it in DAX, so please don't suggest something like table merge. 

Some hints? 

Thankyou!

[andreazambon]

Thank you all for replies. 

Thanks to your work I've discovered that take the information from the OrderTable is not a good choice.

I'll ask your hel again in a different topic.

[ThxAlot · Accepted solution]

[rajendraongole1]

Hi @andreazambon - Please find the below steps to get the expected output for 22/6 and 23/6- average dimensions of Orders produced by Line 1

Model has been defined as per description.

I have designed the same model as per the above description:

create measures to calculate the average order dimensions

AvgOrderDimensionByLine =

CALCULATE(

    AVERAGE(OrderT[kg]),

    USERELATIONSHIP(OrderT[Date], Date_Tprod[DateTable]),

    VALUES(_Produ[Line])

)

getting the same as expected. please check.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!