Forum Discussion

Drewz's avatar
Drewz
Helper II
4 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Drewz
Helper II
4 years ago

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