Forum Discussion

RaptorFox's avatar
RaptorFox
Frequent Visitor
6 years ago
Solved

Calculate an Custom Column based on Values of another Table

Dear Community   I have an issue for displaying a progress of a project compared to its budget. I have two different tables, in one i have the budget, in the other i have all the actuals and the e...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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
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.

  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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 Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RaptorFox's avatar
RaptorFox
Frequent Visitor
6 years ago

Hi v-lionel-msft 

 

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?