Calculate average % using a measure, DISTINCTCOUNT with a FILTER

[ConnieMaldonado]

I have a table with VP, Region, Area, Technicians with sales by date. I have a date slider and an org slicer to look at sales by date range and whatever org hierarchy is desired.

VP	Region	Area	Tech Name	Tech Login	Invoice Date	Sales
North	Connecticut	North Area 1	John Smith	A12345	9/1/2020	$        1,200
North	Connecticut	North Area 1	John Smith	A12345	9/2/2020	$           800
North	Connecticut	North Area 1	John Smith	A12345	9/3/2020	$           775
North	Connecticut	North Area 2	Jose Perez	A23456	9/3/2020	$        2,035
North	Connecticut	North Area 2	Jose Perez	A23456	9/4/2020	$        1,280
North	New York	North Area 3	Jane Smith	B12345	10/3/2020	$           327
North	New York	North Area 3	Jane Smith	B12345	10/8/2020	$        2,055
North	New York	North Area 4	Juan Valdez	B23456	10/15/2020	$        1,645
North	New York	North Area 4	Juan Valdez	B23456	10/20/2020	$        1,835
North	New York	North Area 4	Cindy Jones	C12345	11/1/2020	$        1,190
South	Florida	South Area 1	Ted Hall	D12345	11/2/2020	$           845
South	Florida	South Area 1	Ted Hall	D12345	11/3/2020	$        1,320
South	Florida	South Area 1	Ted Hall	D12345	11/3/2020	$        1,695
South	Florida	South Area 1	Ted Hall	D12345	11/4/2020	$        1,550
South	Florida	South Area 1	Maria Pita	E12345	11/10/2020	$        1,305
South	Florida	South Area 1	Maria Pita	E12345	11/15/2020	$        1,290
South	Florida	South Area 1	Maria Pita	E12345	11/16/2020	$        1,255
South	Florida	South Area 2	John Guthrie	F12345	11/20/2020	$           302
South	Florida	South Area 2	John Guthrie	F12345	11/22/2020	$        2,030
South	Georgia	South Area 2	Larry Alvarez	G12345	11/28/2020	$        1,620
South	Georgia	South Area 2	Larry Alvarez	G12345	11/29/2020	$        1,810
South	Georgia	South Area 2	Larry Alvarez	G12345	12/1/2020	$        1,165
South	Georgia	South Area 2	George Lucas	H12345	12/3/2020	$           820
South	Georgia	South Area 2	George Lucas	H12345	12/3/2020	$        1,295
South	Texas	South Area 3	Brian Dean	J12345	12/4/2020	$        1,670
South	Texas	South Area 3	Brian Dean	J12345	12/5/2020	$        1,525
South	Texas	South Area 3	Brian Dean	J12345	12/7/2020	$        1,280
South	Texas	South Area 3	Charlie Jones	K12345	12/7/2020	$        1,265
South	Texas	South Area 3	Charlie Jones	K12345	12/8/2020	$        1,215
South	Texas	South Area 3	Sara Luz	L12345	12/10/2020	$        1,265

I have created measures to calculate the average weekly sales between the date parameters selected in the slider. The calculations work nicely and show average sales by VP, Region, Area, etc. 

Here are the measures:

DENOMINATOR # Techs = CALCULATE (DISTINCTCOUNT ( SalesByTech[TechLogin] ))

NUMERATOR Sales = Sum(SalesByTech[Sales])

Since I want average weekly sales, I calculated the number of weeks between the dates selected in the slider:

WEEKS Between Slider Dates = DATEDIFF(CALCULATE(MIN('DATE Dim'[DATE]),ALLSELECTED('DATE Dim')),CALCULATE(MAX('DATE Dim'[DATE]),ALLSELECTED('DATE Dim')),WEEK)

Finally, I created a measure to calculate the average sales:

AVERAGE Sales = Divide(SalesByTech[NUMERATOR Sales],(SalesByTech[DENOMINATOR # Techs] * SalesByTech[WEEKS Between Slider Dates]))

I would like to calculate the % of technicians (by VP, Region, etc.) with average sales less than a threshold. So I'm trying to create a measure to count the distinct techs with average weekly sales less than the threshold so I can divide that by the total number of techs and get a percentage.

To calculate the percentage of techs with AVERAGE Sales less than a threshold, I created a measure as follows, but it doesn't work:

# Techs AVG < Threshold = CALCULATE(DISTINCTCOUNT(SalesByTech[TechLogin]), FILTER('SalesByTech',SalesByTech[AVERAGE Sales] < 1300))

What am I doing wrong?

Any help would be appreciated!

[amitchandak · Accepted solution]

@ConnieMaldonado , Try a measure like

# Techs AVG < Threshold = CALCULATE(Countx(FILTER(Values(SalesByTech[TechLogin]),[AVERAGE Sales] < 1300),[TechLogin]))

[ConnieMaldonado]

Wow!  I am in awe.  Your code worked.  Now I must research the difference between countx and distintcount!  😀 Thank you so very much.