Unanswered

Calculated column from multiple tables

(0) Share

Report

Posted on by naaatthh

I want to get the column "Total" in "Table1" to be the calculated sum of "Total" from "Column2", "Column3" and "Column4". So that "Total" in "Table1" is the full sum of the three other totals together. Is this possible? If so, how do I implement it?

Thank you

EDIT: It will also have to be where "ClientID" is the same in all tables.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @naaatthh

You say Sum of column2, column3, column4, are all of these in tables that are referenced as Lookup columns in Table 1? If so then you can reference through those lookups in calculated columns but if these are not lookup columns in Table1 to these other tables, then no you can not do this currently.

Note, PowerFX is coming soon which may open up this capability, not sure yet if it would meet your needs exactly. https://powerapps.microsoft.com/en-us/blog/power-fx-coming-to-model-driven-power-apps-dataverse-and-more/

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
Hi @naaatthh,

As @dpoggemann said, not possible with out-of-the-box calculated columns if the other columns are not lookups on the calculated column's table. The other options are:

Use Power Automate if you don't need the Total to be real-time, so you can calculate when "Column2", "Column3" and "Column4" are updated on Table 1.

If you need the Total be be calculated real-time/synchronisely then you can write a plugin that triggers on update of Table 1 "Column2", "Column3" and "Column4" columns. More info on plugins: https://docs.microsoft.com/powerapps/developer/data-platform/write-plug-in?WT.mc_id=DX-MVP-5004271

Hope this helps!

Was this reply helpful? Yes No
naaatthh 22 on at

Like (0)

Report

@EricRegnier @dpoggemann Thank you both for you reply!

Would these solutions also work if I were trying to add a total from three separate tables into Table 1?

Thanks

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @naaatthh

Only if the relationships were as we discussed with the lookup columns to the other tables on the Table 1. Example is you could have a N:1 from Table 2 to Table 1 with a Lookup column for Table 2 record on Table 1. The calculated field could then reference through this relationship and pull a field from the other table. Same with others... You can not use a calculated field to pull values from other tables otherwise.

As @EricRegnier mentioned as well, you could keep the field as "not calculated" and then utilize Power Automate to pull the values from the other tables based on your own business logic and populate the value on table 1 but this is not real-time and would require the appropriate trigger conditions on anything that could affect the Table 1 value.

Was this reply helpful? Yes No