Forum Discussion
Calculate an Custom Column based on Values of another Table
Hi RaptorFox ,
Try the measure.
Measure = VAR x = CALCULATE( SUM('Time Bookings'[ExternalTotalAmount]), ALLEXCEPT('Project Data', 'Project Data'[ProjectNumber]) ) VAR y = DIVIDE( x, SELECTEDVALUE('Project Data'[Budget]) ) RETURN yBest regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi RaptorFox ,
In "Edit queries", we use M language which doesn't have the SUMX() function.
Power Query M function reference
But we can also use M language grouping aggregation.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k Thanks! Yes, this measure works, i already had it. But now, i would like to somehow calculate and show the percentage between budget and the calculated total amount, therefore i wanted to have the amount directly in the 'Project Data' table.
When i add the measure to the visual, i cannot show the proportion or percentage from the total amount to the budget, i just cannot get it done (might be and most probably is a usage issue):
Community SupportHi RaptorFox ,
Try the measure.
Measure =
VAR x =
CALCULATE(
SUM('Time Bookings'[ExternalTotalAmount]),
ALLEXCEPT('Project Data', 'Project Data'[ProjectNumber])
)
VAR y =
DIVIDE(
x,
SELECTEDVALUE('Project Data'[Budget])
)
RETURN
y
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
With this measure, i was able to at least solve the most pressing business need for information about the project progress, thanks! I know that i was not very clear in stating my expectations in the very beginning and created some confusion. Sorry for that!
Just quickly back to my original question: Why am i not able to add a custom column summarizing all the "ExternalTotalAmount" for each project out of the data from the "Time Bookings" table and add those to the corresponding project in the my "Project Data" table by using the following query:
- v-lionel-msft
Hi RaptorFox ,
In "Edit queries", we use M language which doesn't have the SUMX() function.
Power Query M function reference
But we can also use M language grouping aggregation.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That's a very good and valuable hint. I managed to use the query to add a new table to my data set which is related 1:1 to the "Project Data" table. When i now try to add the summarized value to a new column within "Project Data", i undertstand that with the following query, the whole table is inserted as value of this new column, for each and every row in "Project Data":
= Table.AddColumn(#"Changed Type1", "AggregatedExternalAmount", each Table.Group(#"Time Bookings", {"ProjectNumber"},{{"Sum", each List.Sum([ExternalTotalAmount]),type number}}))So i used the query to create a table on it's own:
What i tried to do now is adding a new row to "Project Data" and using the RELATED function to match the AggregatedExternalAmount to the corresponding project. But process-wise i think this is definitely not best practice, first to create a new table with all the sums for each project and then merge the "AggregatedExternalAmount" and the "Project Data" tables together. What is your take on that?