cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kreiss
Frequent Visitor

Sales by Reps Months of Service

Here's the scenario...

We would like to graphically represent average sales by a rep's length of service (since hired).

 

Tables:

 

Employees (Employee ID, Employee Name, Employee Start Date)
Sales (Date of Sale, Sales Rep ID (Employee ID in the table above), Sales Amount)
Date (Date, Month, Year, etc)

The visual output we are seeking is as follows (yes, terribly dumb data...picture the sales dollars as in the thousands...):

kreiss_0-1640293731292.png

The question we are trying to answer is...how long does it take the average rep to build relationships / etc to maximize their sales.

In an effort to solve this...I built a measure as follows:

Rep Months of Service = 

Var CommissionMonthSinceHire = Average(Date[Date])
Var StartDate = min(Employee[Employee Start Date])

Return DATEDIFF(StartDate,CommissionMonthSinceHire,MONTH)

This calculation works perfectly when I drop it into the "Values" section of a Table or Matrix, but it won't allow me to use the Measure as either the row or the column value of a Matrix...

The end result is...I can calculate an average months worked within a table, but I can't get totals by months worked...

I've been able to answer every question I've ever had by reading others posts and answers...but I can't seem to phrase the question I'm seeking an answer to hear in a way that leads me to the correct answer.

 

Any help is GREATLY appreciated!

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

In order to slice by it you need a calculated column. If I understand the spec I think it needs to go on the Sales table? Eg to say when this sale was made the rep had worked x months.

 

Assume you have Employees 1 => * Sales relationship?

 

In which case create a calculated column in Sales with

Months Employed =

VAR StartDate =
RELATED( Employee[Start Date] )

VAR Result =

DATEDIFF(Sales[Date Of Sales], StartDate, MONTH)

 

RETURN Result

 

Not on a computer so can't test I haven't done something silly.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

I'd like to try.  Share the download link of your PBI file.


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

It looks like you might be able to do static segmentation here, in which case, creating a new calculated column as @bcdobbs suggests may be sufficient (though a configuration dimension table may still be useful).

 

If the bucketing / banding / binning needs to be more of a dynamic segmentation (like if you want to consider months since hired at more than one point in time), then you'll almost certainly need a new configuration dimension table.

 

The keywords I've bolded above and the articles they link to should give you enough vocabulary to seek out all kinds of related questions and answers should you want to research further.

Just for the record, I think I'm pretty smart and know alot.  But I always LEARN more...even though I'm likely not going to use this particular solution for this project, the post and the links were very educational and will absolutely come in handy in the future.

 

Thank you very much @AlexisOlson!

bcdobbs
Super User
Super User

In order to slice by it you need a calculated column. If I understand the spec I think it needs to go on the Sales table? Eg to say when this sale was made the rep had worked x months.

 

Assume you have Employees 1 => * Sales relationship?

 

In which case create a calculated column in Sales with

Months Employed =

VAR StartDate =
RELATED( Employee[Start Date] )

VAR Result =

DATEDIFF(Sales[Date Of Sales], StartDate, MONTH)

 

RETURN Result

 

Not on a computer so can't test I haven't done something silly.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

2 things...

A) Thank you...this solved my problem.
B) I'm a dummy for not thinking of it...it's a solution I've used sooo many times before but I couldn't wrap my head around it at the moment I needed it....

@bcdobbs You Rock!  Solution accepted!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.