Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@ConnieMaldonado , Try a measure like
# Techs AVG < Threshold = CALCULATE(Countx(FILTER(Values(SalesByTech[TechLogin]),[AVERAGE Sales] < 1300),[TechLogin]))
@ConnieMaldonado , Try a measure like
# Techs AVG < Threshold = CALCULATE(Countx(FILTER(Values(SalesByTech[TechLogin]),[AVERAGE Sales] < 1300),[TechLogin]))
Wow! I am in awe. Your code worked. Now I must research the difference between countx and distintcount! 😀 Thank you so very much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |