Calculate an Custom Column based on Values of another Table

[RaptorFox]

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 externally charged amount (ExternalTotalAmount) per time booking.

I would like to add a custom column in the table "Project Data" to calculate percentage and so on to be displayed in a visual.

The following image displays the situation:

Does anyone have a tip how i can achieve my goal?

Thank you very much!

[v-lionel-msft · Accepted solution]

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.

[parry2k]

@RaptorFox very hard to follow your excel sheet, can you past sample data in a table format and explain what is your expected result.

Please share data in excel or share pbix file using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[RaptorFox]

@parry2k Sure, thanks!

Sample Data for Table "Time Bookings"

ProjectNumber	ProjectName	Employee	Date	Hours	ExternalRate	ExternalTotalAmount
651232	Project BI Implementation	Peter Miller	01.04.2020	2.5	200	500
651889	ERP Renewal	Tony West	03.04.2020	5	200	1000
651232	Project BI Implementation	Celine Wild	20.04.2020	3	190	570
…

Sample Data for Table "Project Data"

ProjectNumber	ProjectName	Budget
651232	Project BI Implementation	50000
651889	ERP Renewal	30000
…

I expect a new row in the "Project Data" table which shows the calculated total amount of all the bookings in the "Time Bookings" table for the related project. The screenshot shows the underlying model and the relationship (the key is "ProjectNumber").

ProjectNumber	ProjectName	Budget	UsedAmountTotal
651232	Project BI Implementation	50000	1070
651889	ERP Renewal	30000	1000
…

[parry2k]

@RaptorFox I assume you have one to many relationships between these two tables, just add the following measure and it should work

Used Amount Total = SUM ( TimeBookings[ExternalTotalAmount )

[RaptorFox]

@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):

[v-lionel-msft · Accepted solution]

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.

[RaptorFox]

Hi @v-lionel-msft 

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 · Accepted solution]

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]

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?

[parry2k]

@RaptorFox what you are trying to show is not clear. for % you will do the following

% = DIVIDE ( SUM( TableProject[Budget] ), [SUM Measure of spent amount] )

what is the relevance of count in the visual?