Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You can do this using the query editor and then a DAX column for the End 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 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.
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.
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]
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?
You can do this using the query editor and then a DAX column for the End Value:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |