Forum Discussion
Help! Stuck with something very simple
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!!
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.
2 Replies
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.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.
