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

If you look at my 2 pictures on my last reply, the data shows duplicate in the query editor but not in the visual within the report. If the duplcicates in the query editor concern you then I would do a remove duplicates before applying your changes.

Newbie_77's avatar
Newbie_77
Frequent Visitor
7 years ago

Hi, sorry to bother you again.   Really trying to sort this for my boss !

 

Can you have a look at the excel file stored in this drive link and see if you can replicate the required output ?   (possibly sending back the pbix so that I can see how its done).  You'll notice that multiple items for the same project will have a 'Price' in column R for Day 1 and Day 2.   This data represents that multiple items were delivered to the customer on that day and the original project start value will reduce.  Everything starts at 10000, but this figure seems to multiply once the day 1 and day 2 are merged in. 

 

https://drive.google.com/open?id=1HQmANpuSYg25ptWc2utDZWNyVuxaTkBt

 

I'm really grateful for the help and advice.

 

Phil.

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