Forum Discussion
Not finding Related table or column - Sharepoint List data source
I have 2 tables, both from Sharepoint Lists. One is a Projects table, the other an Invoices table.
I've related the Project ID field from Invoices back to the Project ID PK in the Projects table - this is a many-to-one relationship.
I need to be able to sum up the total value of the invoices related to a project (ie one row in the Project table).
I've tried to do this by adding a new custom column in Projects and using RELATED(), however it's not finding the Invoices table (and therefore I can't select the column for Amount)
Any help would be appreciated
Joe
Hi joemillson,
You can try to write a measure at Project table side:
Use relationship to calculate: Total Value= SUMX(Filter(ALL(Invoices),Invoices[Project ID]= Related(Project[Project ID])),Invoices[Value]) Use current project id to calculate: Total Value= Var currID =LASTNOBLANK(Project[Project ID],[Project ID]) return SUMX(Filter(ALL(Invoices),Invoices[Project ID]= currID),Invoices[Value])
In addition, you can check you relationship setting if the "cross filter direction" option not set to "both".
Regards,
Xiaoxin Sheng
1 Reply
Hi joemillson,
You can try to write a measure at Project table side:
Use relationship to calculate: Total Value= SUMX(Filter(ALL(Invoices),Invoices[Project ID]= Related(Project[Project ID])),Invoices[Value]) Use current project id to calculate: Total Value= Var currID =LASTNOBLANK(Project[Project ID],[Project ID]) return SUMX(Filter(ALL(Invoices),Invoices[Project ID]= currID),Invoices[Value])
In addition, you can check you relationship setting if the "cross filter direction" option not set to "both".
Regards,
Xiaoxin Sheng