Forum Discussion

andreazambon's avatar
andreazambon
Icon for Helper V rankHelper V
2 years ago
Solved

Issue with sum and averages between tables

Hi guys. 

Suppose to have those tables:

 

 

ProductionTable   
Production DateProdOrderLinekg
23/06/20241231100
23/06/20241231100
23/06/20241231100
23/06/20241231100
23/06/20244561150
24/06/20244561150
24/06/20244561150
24/06/20244561150
22/06/20247892200
22/06/20247892200
22/06/20247892200

 

OrderTable 
ProdOrderkgDate
12340022/06/2024
45660023/06/2024
78960022/06/2024

 

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

 

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!

 

Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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!!

     

  • andreazambon's avatar
    andreazambon
    Icon for Helper V rankHelper V
    2 years ago

    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.