Combining Data in Table Visual from Multiple Source Tables

[Drewz]

Hi Power Bi Community,

I'm a bit stuck and I've been going round in circles. Really hope someone can help. 

The data I'm given comes in 3 tables as shown below. If a project is simple, it can just have a project budget, but more complex projects have budgets for each task. I've managed to do a calculated column in the Task Table to create a combined budget either from the Project Budget or the Task Budget. The problem I have is that when I add the data from the Time Entry table, I get all tasks added to all projects whether it was a task for that project or not. And the sum for all tasks is the same which is the total for the project not the task. Below is my desired table output and the what I'm actually getting. My table relationships is also shown below. So what am I doing wrong. I can't see a way to upload my PBIX file, so below is a Google Drive link. 

https://drive.google.com/file/d/1gyFP58FIyTsnJboSi25d3fO5iaFt9R6A/view?usp=sharing

I also want to filter out subsonsultants from the budget, but I'll tackle that another time.

Any help appreciated.

Table 1: Projects
Project ID	Project Name	Project Budget
P1	Design Project
P2	Concept Project	$50
P3	Construction Project

Table 2: Task
Task ID	Task Name	Task Budget	Project ID
T1	Plans	$90	P1
T2	Review	$10	P1
T3	Report		P2
T4	Calculations	$500	P3
T1	Plans	$350	P3
T2	Review	$50	P3
T5	Subconsultant	$100	P3

Table 3: Time Entry
Date	Person	Task ID	Project ID	Charge Amount
14/02/2022	Bob	T1	P1	$20
14/02/2022	Fred	T2	P1	$15
14/02/2022	Betty	T3	P2	$25
14/02/2022	Betty	T1	P3	$200
15/02/2022	Bob	T1	P1	$40
15/02/2022	Bob	T4	P3	$150
15/02/2022	Fred	T2	P3	$60
15/02/2022	Betty	T2	P1	$30
15/02/2022	Betty	T3	P2	$20
15/02/2022	External Subconsultant	T5	P3	$200

Desired Table Visual Output

Project	Task	Budget	Total Charge Amount	Profit / Loss
Design Project	Plans	$90	$60	$30
Design Project	Reviews	$10	$45	-$35
Concept Project	Report	$50	$45	$5
Construction Project	Calculations	$500	$150	$350
Construction Project	Plans	$350	$200	$150
Construction Project	Review	$50	$60	-$10
Construction Project	Subconsultant	$100	$200	-$100

Table Visual I'm getting

Table Relationships

[v-xiaoyan-msft · Accepted solution]

Hi @Drewz ,

You can try the measure below to meet your needs.

BudgetTotal =
SUM ( 'Task'[Task Budget] )
    + CALCULATE (
        SUM ( Projects[Project Budget] ),
        FILTER ( 'Projects', [Project ID] = MAX ( 'Task'[Project ID] ) )
    )

Charge Amount =
CALCULATE (
    SUM ( 'Time Entry'[Charge Amount] ),
    FILTER (
        'Time Entry',
        'Time Entry'[Project ID] = MAX ( 'Projects'[Project ID] )
            && 'Time Entry'[Task ID] = MAX ( 'Task'[Task ID] )
    )
)

PL = [BudgetTotal]-[Charge Amount]

Then you can get a result like this：

A demo for your reference is attached.

Hope it helps,

Community Support Team _ Caitlyn

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

[Drewz]

Hi Caitlyn,

Actually, a small fly in the ointment. I went to convert the table to a Matrix. Throught that would be a tidier layout. But the column total for the Charge Amount is not the total of all the tasks, just one, presumably the task with the max task ID. Is there any way to get the Charge Amount in Matrix format to sum all the tasks?

Cheers Drew

[Drewz]

Ahhh, genius thank you so much!!!!! Needed that filter context.

I did find that sometimes there is both a Project Budget and a Task Budget, so I just needed to add the IF() statement below.  Not sure if that is the most elegant code, but it worked. 

BudgetTotal =
IF( SUM( 'Task' [Task Budget]) > 0,
SUM('Task' [Task Budget])),
SUM ( 'Task'[Task Budget] )
    + CALCULATE (
        SUM ( Projects[Project Budget] ),
        FILTER ( 'Projects', [Project ID] = MAX ( 'Task'[Project ID] ) )
    )
)

[Drewz]

Just to clarify, then relationships are linked to the Proejcts table via Project ID. I can't link Task IDs in Time Entry and Tasks as it would be a many to many relationship.