cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnieMaldonado
Resolver II
Resolver II

Calculate average % using a measure, DISTINCTCOUNT with a FILTER

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.

VPRegionAreaTech NameTech LoginInvoice DateSales
NorthConnecticutNorth Area 1John SmithA123459/1/2020 $        1,200
NorthConnecticutNorth Area 1John SmithA123459/2/2020 $           800
NorthConnecticutNorth Area 1John SmithA123459/3/2020 $           775
NorthConnecticutNorth Area 2Jose PerezA234569/3/2020 $        2,035
NorthConnecticutNorth Area 2Jose PerezA234569/4/2020 $        1,280
NorthNew YorkNorth Area 3Jane SmithB1234510/3/2020 $           327
NorthNew YorkNorth Area 3Jane SmithB1234510/8/2020 $        2,055
NorthNew YorkNorth Area 4Juan ValdezB2345610/15/2020 $        1,645
NorthNew YorkNorth Area 4Juan ValdezB2345610/20/2020 $        1,835
NorthNew YorkNorth Area 4Cindy JonesC1234511/1/2020 $        1,190
SouthFloridaSouth Area 1Ted HallD1234511/2/2020 $           845
SouthFloridaSouth Area 1Ted HallD1234511/3/2020 $        1,320
SouthFloridaSouth Area 1Ted HallD1234511/3/2020 $        1,695
SouthFloridaSouth Area 1Ted HallD1234511/4/2020 $        1,550
SouthFloridaSouth Area 1Maria PitaE1234511/10/2020 $        1,305
SouthFloridaSouth Area 1Maria PitaE1234511/15/2020 $        1,290
SouthFloridaSouth Area 1Maria PitaE1234511/16/2020 $        1,255
SouthFloridaSouth Area 2John GuthrieF1234511/20/2020 $           302
SouthFloridaSouth Area 2John GuthrieF1234511/22/2020 $        2,030
SouthGeorgiaSouth Area 2Larry AlvarezG1234511/28/2020 $        1,620
SouthGeorgiaSouth Area 2Larry AlvarezG1234511/29/2020 $        1,810
SouthGeorgiaSouth Area 2Larry AlvarezG1234512/1/2020 $        1,165
SouthGeorgiaSouth Area 2George LucasH1234512/3/2020 $           820
SouthGeorgiaSouth Area 2George LucasH1234512/3/2020 $        1,295
SouthTexasSouth Area 3Brian DeanJ1234512/4/2020 $        1,670
SouthTexasSouth Area 3Brian DeanJ1234512/5/2020 $        1,525
SouthTexasSouth Area 3Brian DeanJ1234512/7/2020 $        1,280
SouthTexasSouth Area 3Charlie JonesK1234512/7/2020 $        1,265
SouthTexasSouth Area 3Charlie JonesK1234512/8/2020 $        1,215
SouthTexasSouth Area 3Sara LuzL1234512/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!

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@ConnieMaldonado , Try a measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@ConnieMaldonado , Try a measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.