SUMX with multiple tables

[juanjosegarrido]

Dear Community,

There is a formula I have been looking for and can''t figure it out. I have several tables in my model for Projects, Contracts and percentage progress as planned and real progress. 

Formula I am looking for X = Amount(%RealProgress - %PlannedProgress). Formula X contains 3 fields, each of them in a different table.

I have been trying things like creating a measures with Sumx, Related and RelatedTable like example below with no luck.

 CALCULATE(SUMX(RELATEDTABLE(tblRealProgress);tblRealProgress[Progress]*RELATED(tblContracts[Amount])) - 

                     SUMX(RELATEDTABLE(tblContracts);tblContracts[Amount])*SUM(tblPlannedProgress[Progress])) 

Data model in image below.

Any ideas are very much appreciated.

Regards.

[v-sihou-msft]

@juanjosegarrido

In this scenario, your Amount can map RealProgress properly. However, your Project has 1 to many relationship to plannedProgress, which means your Amount can never get a specific corresponding PlannedProgress. This is the reason why your formula can't work.

Regards,

[juanjosegarrido]

Hi, thanks for your reply. Because there are more than one contract value, what would be a correct change in my data model?

Maybe moving contract amount to Projects table?

Regards.

[v-sihou-msft]

@juanjosegarrido

In this scenario, your Amount can map RealProgress properly. However, your Project has 1 to many relationship to plannedProgress, which means your Amount can never get a specific corresponding PlannedProgress. This is the reason why your formula can't work.

Regards,