Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Drewz
Helper II
Helper II

Combining Data in Table Visual from Multiple Source Tables

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 IDProject NameProject Budget
P1Design Project 
P2Concept Project$50
P3Construction Project 

 

Table 2: Task   
Task IDTask NameTask BudgetProject ID
T1Plans$90P1
T2Review$10P1
T3Report P2
T4Calculations$500P3
T1Plans$350P3
T2Review$50P3
T5Subconsultant$100

P3

 

Table 3: Time Entry    
DatePersonTask IDProject IDCharge Amount
14/02/2022BobT1P1$20
14/02/2022FredT2P1$15
14/02/2022BettyT3P2$25
14/02/2022BettyT1P3$200
15/02/2022BobT1P1$40
15/02/2022BobT4P3$150
15/02/2022FredT2P3$60
15/02/2022BettyT2P1$30
15/02/2022BettyT3P2$20
15/02/2022External SubconsultantT5P3$200

 

Desired Table Visual Output

ProjectTaskBudgetTotal Charge AmountProfit / Loss
Design ProjectPlans$90$60$30
Design ProjectReviews$10$45-$35
Concept ProjectReport$50$45$5
Construction ProjectCalculations$500$150$350
Construction ProjectPlans$350$200$150
Construction ProjectReview$50$60-$10
Construction ProjectSubconsultant$100$200-$100

 

Table Visual I'm getting

Table Visual Output.jpg

Table Relationships

Table Relationships.jpg

1 ACCEPTED SOLUTION
v-xiaoyan-msft
Community Support
Community Support

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:

 

vcaitlynmstf_0-1645522967287.png

 

 

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.

View solution in original post

4 REPLIES 4
v-xiaoyan-msft
Community Support
Community Support

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:

 

vcaitlynmstf_0-1645522967287.png

 

 

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] ) )
    )
)

 

Drewz
Helper II
Helper II

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors