Forum Discussion
Calculating Revenue/Time with Revenue table and Time table
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!
I'd suggest doing the Append Queries as New and then grouping by Client ID rather than removing duplicates.
4 Replies
- AlexisOlson
Super User
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!
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 🙂
