Skip to main content
cancel
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

Reply
jdellamag
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.

1 ACCEPTED SOLUTION

Hi,

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

Hope this helps.

Untitled.png


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

View solution in original post

3 REPLIES 3
NaveenGandhi
Super User
Super User

@jdellamag 

 

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

 

Thanks,

Naveen

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

Hi,

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

Hope this helps.

Untitled.png


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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors