Help! Stuck with something very simple

[Nic99999]

Hi 

Im stuck with something on a bigger data set but if you can help with this example it should fix my problem! 

I have two tables in my model.  One is a list of clients with a total target for sales for the year.  The second table is a list of projects that are being done for a client with actual revenue against each project.   There is a one to many relationship between the client table and the project table.  

All I want to be able to do is add a column to the target table which shows the sum of the actuals for projects that belong to each client.  I just cant seem to do it even though I can do it with a visual I need to have it in a column for some other calculations. 

Tables are:

Client Budget

Client	Budget
A	5000
B	2000
C	3000

Actuals for Project

Client	Project Name	Actual
A	One	100
B	Two	200
B	Three	300
B	Four	400
C	Five	500
C	Six	600
C	Seven	700
A	Eight	800

This is the result I want with the new column on the right.  I cant use related as it is on the one side of a one to many and I want the sum of all the actuals for projects in the second table where the client is the same as the first column in the first table.  Put simply, I want it to show the total actuals for each client.   

Client	Budget	Acutals
A	5000	900
B	2000	900
C	3000	1800
		3600

Many thanks and super grateful for any help as I am now to this!!

[Anonymous · Accepted solution]

Hi @WishAskedSooner ,

Thank you for your very quick and effective replies.

Hi @Nic99999 ,

I will provide two options for you to choose from, one for creating calculated columns and one for creating measure.

1. below is my model

Table:

Table2:

2. you could create measure or column with below dax formula

Column =
VAR tmp = RELATEDTABLE ( 'Table 2' ) RETURN SUMX ( tmp, [Actual] )

Measure = SUM('Table 2'[Actual])

add a table visual with fields and measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WishAskedSooner]

If you want a New Column (calculated column) in your 'Client Budget' table that sums the 'Actuals' column in the 'Actuals for Project' table by 'Client' which is joined in the data model, try entering the following DAX in the formula bar after clicking New Column:

Actuals = SUMX(FILTER('Actuals for Project', 'Actual for Project'[Client] = 'Client Budget'[Client]), [Actual])

However, you said you are working with bigger data. And one of the cardinal sins of any data model (databases, Power  BI, etc.) is to join tables on Text columns as this is slower when filtering, sorting, joining, etc. You should have a Client ID table with the Client name and join on the ID everywhere, replacing any tables that have Client name with Client ID.