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

     

  • Drewz's avatar
    Drewz
    Helper II
    4 years ago

    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