Sales by Reps Months of Service

[kreiss]

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...):

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!

[Ashish_Mathur]

Hi,

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

[AlexisOlson]

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.

[kreiss]

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 · Accepted solution]

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.

[kreiss]

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!