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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Revenue Count Deduplication

Hi there,


I am dealing with a data calculation situation that I just can't seem to solve. We are in the business of staffing/recruitment and I need to calculate the total revenue being brought in by a team member on all jobs they are associated with.


The scenario:    The team members are assigned to a "Search", at which point they work with the client to fill an open role - these searches have Unique IDs.  We bill the client in installments, anywhere between 2 - 6 times, but this varies search by search.  In our system we record a 1st Bill Amount, 1st Bill Date, 2nd Bill Amount, 2nd Bill Date, 3rd Bill Amount, 3rd Bill Date, etc.  - all the way through 6th Bill.  These are all individual columns in my data table.  Beyond that, the team member can be working in any of 4 roles with regard to a single search being worked:  Search Leader 1, Search Leader 2, Opportunity Owner 1, Opportunity Owner 2.  These are also columns in the table.  Where things get confusing is that technically an employee could be listed in more than 1 role, so for example someone could be a Search Leader 1 and an Opportunity Owner 2.  In this instance, I need to be able to calculate the revenue brought in by this person, but not have it duplicated because their name is listed twice.  Please find an example data table below.


My ultimate goal is to be able to report on how much revenue is billed in each month, but be able to slice that by the employee without counting double revenue where they are listed under 2 different roles.


Search IDSearch TitleClientSearch Leader 1Search Leader 2Opportunity Owner 1Opportunity Owner 21st Bill Amount1st Bill Date2nd Bill Amount2nd Bill Date3rd Bill Amount3rd Bill Date4th Bill Amount4th Bill Date5th Bill Amount5th Bill Date6th Bill Amount6th Bill Date
234jdwManagerClient AJohnSallyJohnElizabeth $              1,0001/3/2023 $               1,0001/29/2023 $                  5004/3/2023      
544kjsDirectorClient BWayneRandyPhil  $              3,00012/4/2022 $               1,0002/23/2023 $               1,0003/26/2023 $              1,0003/29/2023 $              1,0004/12/2023  
212duyManagerClient CSallyJaneJaneJoe $              1,0003/21/2023 $                   5004/1/2023 $               1,0004/15/2023 $              1,0004/23/2023    
783hfrManagerClient ASallyJohnPhilJohn $                 7502/2/2023 $               3,0003/7/2023        


Thanks in advance, and let me know if I can provide any additional clarification or detail.



Sally's answer should be 6,750.  You may download my PBI file from here.

Hope this helps.


Ashish Mathur

View solution in original post

Super User
Super User



Can you provide the desired output with regards to the sample data you have provided?




Sorry about that, here are the desired outputs:


Billings for Q1 (Jan 1 - Mar 31 2023)
John $                         5,750
Sally $                         3,000
Elizabeth $                         2,000
Wayne $                         3,000
Randy $                         3,000
Phil $                         6,750
Jane $                         1,000
Joe $                         1,000


Sally's answer should be 6,750.  You may download my PBI file from here.

Hope this helps.


Ashish Mathur

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.