Reply
Highlighted
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 
RevenueNumber InternalRepKeyDistinct String
InvoiceIDString InternalRepIDString
InternalRepKEYString   
ExternalRepKEYString   
   ExternalRep Table   
InternalRepKEY <> External RepKEY  ExternalRepKeyDistinct String
   ExternalRepIDString
     
Need to bring in InvoiceID and Revenue where InternalRepID <> ExternalRepID
Super User
Posts: 3,944
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,542
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.

9.png

 

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]))

10.png

 

Best Regards

Maggie

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

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

@Ashish_Mathur 

 

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 
RevenueInvoiceIDInternalRepKEYExternalRepKEY InternalRepKeyInternalRepIDEnternalRepKeyEnternalRepID
100013012 30MJ 12SR
70022537 25RT 37RT
20031543 15DD 43DD
600479 7SR 9LY
          
          
Expected Results         
Sales where Internal Rep ID <> External Rep ID       
          
InvoiceIDInternalRepIDEnternalRepIDRevenue      
1MJSR1000      
4SRLY600      
Member
Posts: 87
Registered: ‎05-21-2018

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

@v-juanli-msft,

 

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