Measure with one to many relationship

[GuillaumeBDddd]

Hello, I'm trying to find a way to get by Project, the Sum of Qte per BC from table "Orders". So if a project has a link to a BC, I need to see the total quantity of the BC (See Table below in green).

No matter what measure I use, I always get 23 wich is the grand total of table "Orders". (See Table below in red)

Any idea how to do it with a measure? Here's the model (there are two one to many relationships).

Thank you

[rajendraongole1 · Accepted solution]

Hi @GuillaumeBDddd -you can create a measure that calculates the sum of quantities (Qte) from the "Orders" table, based on the BC value associated with each project in the "Projects" table.

Since there’s a Link Table between the "Orders" and "Projects" tables, we’ll need to use it in the measure to filter data correctly.

I hope in your model view the relationships between "Projects" and the Link Table and between "Orders" and the Link Table are active.

check the datatype for BC too in all tables.

try below measure :

Total Qte by Project =
CALCULATE(
SUM(Orders[Qte]),
TREATAS(VALUES(Projects[BC]), LinkTable[BC])
)

This works, try above calculation.