Calculating Revenue/Time with Revenue table and Time table

[mmk_PB]

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!

[AlexisOlson · Accepted solution]

I'd suggest doing the Append Queries as New and then grouping by Client ID rather than removing duplicates.

[mmk_PB]

Thanks! I was looking for a clean solution like this!

[Fowmy]

@mmk_PB 

I was able do the same steps as you have done,

File attached below

[StenbergAleksi]

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 🙂