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 folks,
I have recently encountered an issue when calculating profitability in Power BI desktop.
Below are the two tables to start with. Note that a Client ID may:
1. Only exist in the Rev Table
2. Only exist in the Time Table
3. Exists in both tables
Rev Table
Client ID | Revenue |
1 | 50 |
2 | 20 |
2 | 20 |
3 | 50 |
4 | 50 |
Time Table
Client ID | Time |
1 | 0.5 |
2 | 1 |
3 | 1 |
3 | 1 |
5 | 0.5 |
I would like to achieve below:
Revenue/Time Table
Client ID | Revenue | Time |
1 | 50 | 0.5 |
2 | 40 | 1 |
3 | 50 | 2 |
4 | 50 | 0 |
5 | 0 | 0.5 |
I created a unique list of Client ID by appending the two tables as New (and removed duplicated Client ID and removed errors). However, I was unable to create an "One-to-many" relationship between the new table and the table or the Time table.
I am relatively new to Power BI so I might not be using the easiest approach. Any suggestion/help is greatly appreciated!
Solved! Go to Solution.
I'd suggest doing the Append Queries as New and then grouping by Client ID rather than removing duplicates.
I'd suggest doing the Append Queries as New and then grouping by Client ID rather than removing duplicates.
Thanks! I was looking for a clean solution like this!
@mmk_PB
I was able do the same steps as you have done,
File attached below
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @mmk_PB ,
If you just want to create the Revenue/Time table from two tables you provided. You can use merge ables as new and based on client id column.
Then the result will be as you provided.
If this solved the issue, please accept it as a solution 🙂
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |