Forum Discussion
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:
- In the query editor select Merge Queries as New
- 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.
- 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:
- 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:
- Repeat step 3 on the Table3 column
- Delete the 2 extra project number columns and rename the other 2 columns
- 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] - Output:
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 after clicking double arrows;
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:
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?
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:
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?
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 ???
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.
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:
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?