## Desktop

Member
Posts: 87
Registered: ‎05-21-2018

# Calculate Sum of Revenue where Strings from two other tables match or do not match

I have always had a hard time using non-distinct columns in filtered measures.

I need to calculate revenue where internal and external Sales Rep IDs are not the same.

I can do that using the SalesRepKEYs in the Sales table but the Keys for Internal and External reps are not the same for the Reps.

I need to use the RepIDs (where InternalRepID <> ExternalRepID )

The Tables are linked via the Internal and External RepKEY.

 Sales Table InternalRep Table Revenue Number InternalRepKey Distinct String InvoiceID String InternalRepID String InternalRepKEY String ExternalRepKEY String ExternalRep Table InternalRepKEY <> External RepKEY ExternalRepKey Distinct String ExternalRepID String Need to bring in InvoiceID and Revenue where InternalRepID <> ExternalRepID
Super User
Posts: 3,943
Registered: ‎01-14-2017

## Re: Calculate Sum of Revenue where Strings from two other tables match or do not match

Hi,

Share some data and show the expected result.

Community Support Team
Posts: 2,529
Registered: ‎03-15-2018

## Re: Calculate Sum of Revenue where Strings from two other tables match or do not match

Hi @Moscuba

I make a test with the following tables, if my data example is not as yours, please let me know.

Create measures in "Sales Table" to get the "InternalRepID" and "ExternalRepID" assicated with the "InternalRepKey" and "ExternalRepKey" in  "Sales Table".

lookup_internalid = LOOKUPVALUE('InternalRep Table'[InternalRepID],'InternalRep Table'[ InternalRepKey],MAX('Sales Table'[InternalRepKEY]))

lookup_externalid = LOOKUPVALUE('ExternalRep Table'[ExternalRepID],'ExternalRep Table'[ExternalRepKey],MAX('Sales Table'[ExternalRepKEY]))

then create a measure to sum the "Revenue" where InternalRepID <> ExternalRepID,

sum1 calculate the sum for each row and let the cells as spacewhere InternalRepID = ExternalRepID,

sum2 calculate the sum for all rows and let the cells as spacewhere InternalRepID = ExternalRepID, when you add it in the table visual, it will show many rows than expected, you could set "sum1" in Visual level filter" as "show items when value is not blank".

sum1 = CALCULATE(SUM('Sales Table'[Revenue]),FILTER('Sales Table',[lookup_internalid]<>[lookup_externalid]))

sum2 = CALCULATE(SUM('Sales Table'[Revenue]),FILTER('Sales Table',[lookup_internalid]<>[lookup_externalid]))

Best Regards

Maggie

Member
Posts: 87
Registered: ‎05-21-2018

## Re: Calculate Sum of Revenue where Strings from two other tables match or do not match

I posted sample data and expected results. It did not appear to link to your message.

Member
Posts: 87
Registered: ‎05-21-2018

## Re: Calculate Sum of Revenue where Strings from two other tables match or do not match

 SAMPLE DATA Sales Table InternalRep Table ExternalRep Table Revenue InvoiceID InternalRepKEY ExternalRepKEY InternalRepKey InternalRepID EnternalRepKey EnternalRepID 1000 1 30 12 30 MJ 12 SR 700 2 25 37 25 RT 37 RT 200 3 15 43 15 DD 43 DD 600 4 7 9 7 SR 9 LY Expected Results Sales where Internal Rep ID <> External Rep ID InvoiceID InternalRepID EnternalRepID Revenue 1 MJ SR 1000 4 SR LY 600
Highlighted
Member
Posts: 87
Registered: ‎05-21-2018

## Re: Calculate Sum of Revenue where Strings from two other tables match or do not match

It works. Unfortunately it is extremely slow (millions of records).