Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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.
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
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] ) )
)
)
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.