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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Newbie_77
Frequent Visitor

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.   I have table 2 and 3 joined to table 1 via 'Project Number'.  Cannot relate Table 2 and 3 via Project number becuase its requires unique values.

 

Table 1 - contains Project Number, Start Value.

Table 2 - contains Project Number, Value (Day1)

Table 3 - contains Project Number, Value (Day2)

 

Output required;

 

Project Number     Start Value     Value Day1     Value Day2    End Value

 

 12345                     10000            1000               1000              8000

 34567                      500                150                                      350

 

 

I can build a table with Project Number, Satrt Value, Value 1 and Value 2, but cannot create either new column or measure to SUM each individual Day (as they are from different unrelated tables), then deduct from Start Value to arrive at the End Value.  Or of course End Value = Sart Value minus Day 1 & Day 2.

 

Any help greatly appreciated.

 

Phil.

13 REPLIES 13
Anonymous
Not applicable

You can do this using the query editor and then a DAX column for the End Value:

 

  1. In the query editor select Merge Queries as New
  2. Use Table1 as your top table and Table2 as your bottom table and select the Project number on both tables. Select Full Outer as your join type.
    1.PNG
  3. In the new Merge1 table hit the double arrow button next to the Table2 comlumn heading and click OK on the window that pops up:
    2.PNG
  4. Now select the Merge Queries option (not Merge Queries as New) and join the new Merge1 table with Table3 the same way you did on the first merge:
    3.PNG
  5. Repeat step 3 on the Table3 column
  6. Delete the 2 extra project number columns and rename the other 2 columns
    4.PNG
  7. After you close and apply, create a new column in the Merge1 table using this DAX:
    End Value = Merge1[Start Value] - Merge1[Value Day 1] - Merge1[Value Day 2]
  8. Output:
    5.PNG

Thank you so much, nearly there.

 

Because my Value Day1 data may have multiple rows that total the value, it appears to duplicate the Start Value when I merge using the double arrow.

 

So, in the sample for Project 12345, I might have 2 rows of data that equal the 1000 value (2 rows of 500 for that project on that day).  The Power BI table auto sums for that Project, so Day1 does show 1000 which is great.  BUT, the merge process seems to add multiple rows for the Start Value.

 

First image below shows the initial merge process where each company starts with 10000.   2nd image shows that it duplicates the value (multiple entires for some companies).  When I add this to a table visualisation, it sums the totals, so Company 3 shows as 20000.   If I change to 'dont Summarize', it alters the display of each entry back to 10000, but the new End Value still uses the 20000.

 

Merge Start

 

Merge start.PNG

 

Merge after clicking double arrows;

 

Merge.PNG

Anonymous
Not applicable

Then either we are doing something a little bit differently in our examples or I'm not understanding yourdesired results. I added a duplicate value for project 12345 in Table2 with a Value Day 1 of 1000:

1.PNG

 

in my output visuals i have all values set to not summarize. You'll see that I only have one entry in table2 for project 12345 with a value day 1 of 1000 (not 2000). In my merge1 table it used the 1000 in value day 1 and 2 to calculate my end value (10000 - 1000 - 1000 = 8000). What am I missing?

2.PNG

Anonymous
Not applicable

Then either we are doing something a little bit differently in our examples or I'm not understanding yourdesired results. I added a duplicate value for project 12345 in Table2 with a Value Day 1 of 1000:

1.PNG

 

in my output visuals i have all values set to not summarize. You'll see that I only have one entry in table2 for project 12345 with a value day 1 of 1000 (not 2000). In my merge1 table it used the 1000 in value day 1 and 2 to calculate my end value (10000 - 1000 - 1000 = 8000). What am I missing?

2.PNG

Hi, many thanks for your help on this.  still struggling !

 

3 images below show how it breaks at some point.   The Original merge shows the OrdersStart table (first table), merging with my Day1 table.  You'll notice that the OrdersStart is nice and simple, 10000 for 5 companies.  When merged by results in the second image and all still appears OK.  when then clicking the doible arrow and OK on next step, image 3 shows that the original Customer Start Values have duplicated - multiple Company 4 with 10000 each as a start value.

 

Am I doing something wrong ???

 

 

Merge.PNG

 

 

merge1.PNG

 

 

 

 

merge2.PNG

Anonymous
Not applicable

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.

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
Not applicable

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.

Thank you.  Amended and uploaded back into the folder.

 

 

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]

Anonymous
Not applicable

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.

Anonymous
Not applicable

Then either we are doing something a little bit differently in our examples or I'm not understanding yourdesired results. I added a duplicate value for project 12345 in Table2 with a Value Day 1 of 1000:

1.PNG

 

in my output visuals i have all values set to not summarize. You'll see that I only have one entry in table2 for project 12345 with a value day 1 of 1000 (not 2000). In my merge1 table it used the 1000 in value day 1 and 2 to calculate my end value (10000 - 1000 - 1000 = 8000). What am I missing?

2.PNG

Anonymous
Not applicable

You can do this using the query editor and then a DAX column for the End Value:

 

  1. In the query editor select Merge Queries as New
  2. Use Table1 as your top table and Table2 as your bottom table and select the Project number on both tables. Select Full Outer as your join type.
    1.PNG
  3. In the new Merge1 table hit the double arrow button next to the Table2 comlumn heading and click OK on the window that pops up:
    2.PNG
  4. Now select the Merge Queries option (not Merge Queries as New) and join the new Merge1 table with Table3 the same way you did on the first merge:
    3.PNG
  5. Repeat step 3 on the Table3 column
  6. Delete the 2 extra project number columns and rename the other 2 columns
    4.PNG
  7. After you close and apply, create a new column in the Merge1 table using this DAX:
    End Value = Merge1[Start Value] - Merge1[Value Day 1] - Merge1[Value Day 2]
  8. Output:
    5.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.