Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |