cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Newbie_77 Frequent Visitor
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
kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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
kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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
Newbie_77 Frequent Visitor
Frequent Visitor

Re: Sum Rows from Different Tables, Deduct from Start Value

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

kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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

kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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

Highlighted
kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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

Newbie_77 Frequent Visitor
Frequent Visitor

Re: Sum Rows from Different Tables, Deduct from Start Value

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

kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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.

kleetus51 Member
Member

Re: Sum Rows from Different Tables, Deduct from Start Value

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 261 members 3,086 guests
Please welcome our newest community members: