Sales By People Not Trained Based on Slicer Selections

[EDWNW]

I am trying to determine if the various trainings my organization has been offering have been effective or not. To that end I have two tables:

1. Order Details - this contains the employee IDs and their sales by deal, such as this:

Sales ID	Employee ID	Sales Amount
1234	555	20
4567	555	30
8910	121	15
6548	121	40
1346	987	30

2. Trainings - this shows which employees have taken which training

Training Name	Employee ID
Sell More	555
Sell More	121
Making Money	555
Building Networks	121

There is an inactive relationship between 'Order Details'[Employee ID] and 'Trainings'[Employee ID]. 'Trainings' filters 'Order Details'. This relationship is inactive because there is another table filtering both the 'Order Details' and 'Trainings' which is active, but let's ignore that for now to keep things as simple as possible.
I also have a slicer, 'Training'[Training Name], so people using this report can select the training(s) they are interested in. They may only select one training, or they may select multiple trainings.

One way to show if the trainings have been effective is by looking at the average deal size by people who have taken a training and those who have not, to that end I have put together these measures:

Average Deal Size = AVERAGEX(DISTINCT('Order Details'[Sales ID]),[Total Revenue])

Trained Sales Rep Avg Deal Size = CALCULATE([Average Deal Size],USERELATIONSHIP('Order Details'[Employee ID],'Trainings'[Employee ID]))

Not Trained Rep Avg Deal Size = CALCULTATE([Average Deal Size],????????

Can anyone help with the Not Trained Rep Avg Deal Size? What filters should I apply here?

One idea I had was use LOOKUPVALUE=BLANK() such as:

Not Trained Rep Avg Deal Size =

var SelectedClass = VALUES('Trainigs'[Training Name])

return
CALCULATE([Average Deal Size], FILTER('Order Details', LOOKUPVALUE(Trainings[Employee ID],Trainings[Employee ID],'Order Details'[Employee ID],Trainings[Training Name],SelectedClass)=BLANK()))

This of course results in an error, that multiple values were supplied when one was expected. 

To put what I want into words: for an employee in the 'Order Details' table, have they taken one of the courses selected in the slicer? If they haven't then include their sales in the average.

Some example outcomes:

For "Sell More"

Trained Sales Rep Avg Deal Size = 26.25

Not Trained Rep Avg Deal Size = 30.00

For "Making Money"

Trained Sales Rep Avg Deal Size = 25.00

Not Trained Rep Avg Deal Size = 28.33

For "Sell More" AND "Building Networks"

Trained Sales Rep Avg Deal Size = 26.25

Not Trained Rep Avg Deal Size = 30.00

Thanks for any help or guidance you can provide.

[mahoneypat · Accepted solution]

Not sure if this is exactly what you are looking for but here in an approach that works with no relationship between your Sales and Training tables.  Make a table with your Training Course names and these two measures:

Trained =
CALCULATE (
SUM ( Sales[Sales Amount] ),
TREATAS ( VALUES ( Training[Employee ID] ), Sales[Employee ID] )
)

Not Trained =
CALCULATE (
SUM ( Sales[Sales Amount] ),
EXCEPT (
VALUES ( Sales[Employee ID] ),
TREATAS ( VALUES ( Training[Employee ID] ), Sales[Employee ID] )
)
)

To be able to get total sales for those trained and not trained that looks like this:

This will work with one or more training courses selected.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[EDWNW]

This is great, works like a charm.

Thank you.