Forum Discussion
SUMX with multiple tables
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.
3 Replies
v-sihou-msftMicrosoft Employee
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,
- v-sihou-msft
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,
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.
