Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
5 REPLIES 5
Anonymous
Not applicable

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      
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

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

Anonymous
Not applicable

@v-juanli-msft,

 

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

Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.