Forum Discussion

Newbie_77's avatar
Newbie_77
Frequent Visitor
7 years ago

Sum Rows from Different Tables, Deduct from Start Value

Hi All,   I'm sure this is really simple, searched and found similar but not the right answer.   I have 3 data source tables, combined into a BI table.  I need to add some totals plus deductions....
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

I can but do me a couple of favors. There are a lot of extra columns in there that aren't part of the merging we're trying to do. Please delete them so I don't get mixed up. Also, replicate the expected output in a separate worksheet. Then reports the file and I'll take a look.

Newbie_77's avatar
Newbie_77
Frequent Visitor
7 years ago

Thank you.  Amended and uploaded back into the folder.

 

 

  • MarkLaf's avatar
    MarkLaf
    Super User
    7 years ago

    In order to leverage the data modeling of PBI, I would recommend you don't try to merge all your tables together to get what you want.

     

    You essentially have two tables from what I see: 1) a 'Projects' table where there is one row per project along with various project-level data including the project budget (I'm assuming), and 2) a 'Transactions' table with multiple rows/transactions per project - i.e. you should combine the Day 1 and Day 2 tables into one transactions table.

     

    If you load the two tables into the data model and set up a relationship on the project ID, this simplifies your problem a great deal.

     

    Add the below calculated columns to your Projects table:

     

    TotalSpend

    = CALCULATE(SUM(Transactions[Value]))

     

    RemainingBudget

    = [Start Value] - [TotalSpend]