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

jasmin_w — Wed, 03 Jul 2024 14:13:40 GMT

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:

Sales Roster - contains one row per Employee
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:

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

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

Anonymous — Thu, 04 Jul 2024 05:41:19 GMT

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.

topic Re: Calculated Column - SUM values in another table based on IDs equal to each other in DAX Commands and Tips

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

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