Calculated Column - SUM values in another table based on IDs equal to each other

[jasmin_w]

I am trying to create a calculated column based on the SUM of values in another table where the IDs match each other. 

I have 2 tables: 

1. Sales Roster - contains one row per Employee
2. Revenue - contains all revenue data with multiple rows per Employee with multiple employee columns.

There is already an existing many-to-one relationship between 'Revenue[Geo Owner ID] and 'Sales Roster'[EmpID]. Most revenue is reported this way and under this relationship. This is considered Core revenue and all revenue is summed under the 'Sales Roster'[EmpID].

Additionally I would like to create a calculated column that reports on the revenue a different way, which is Collaboration revenue. I pretty much want it to do this:

CALCULATE ( SUM ('Revenue'[Revenue USD]), 'Revenue'[Account Owner ID] = 'Sales Roster'[EmpID] ) )

Unfortuntately, it has not been this simple because the 2 tables already have an existing relationship and the 'Revenue' table has many duplicates in the 'Revenue'[Account Owner ID]. 

Any help would be appreciated, thank you!

Some methods I have attempted:

1. VAR _revenueID = FIRSTNONBLANK('Revenue'[Account Owner ID], 1)
       VAR _rosterID = FIRSTNONBLANKVALUE('Sales Roster'[EmpID] , 1)
       RETURN
       SUMX(
       FILTER( 'Revenue', _revenueID = _rosterID),
       'Revenue'[Revenue_USD] )
2. CALCULATE(
   SUM( 'Revenue'[Revenue_USD),
   'Sales Roster'[EmpID] = FIRSTNONBLANK('Revenue'[Account Owner ID], 1 )
   )

[Anonymous · Accepted solution]

Hi @jasmin_w ,

I create two tables as you mentioned.

Then I create two calculated columns and get what you want.

Column = 
SUMX ( FILTER ( 'T2', 'T2'[Peiod] = 'T1'[Peiod] ), 'T2'[Amount] )

Column 2 = 
SUMX ( FILTER ( 'T1', 'T1'[Peiod] <= EARLIER ( T1[Peiod] ) ), 'T1'[Column] )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.