I have a table that has revenue amounts by day, by technician. Each records has an associated Line of Business (LOB). I also have a date slicer and an org slicer.
| RDO Name | Site Manager | Location | LOB | Tech Name | TechLogin | Status | Revenue | Invoice Date |
| RDO 1 | SM 1 | LOC 1 | LOB 1 | John Smith | 123456 | Active | $90 | 2/5/21 |
| RDO 1 | SM 1 | LOC 2 | LOB2 | John Smith | 123456 | Active | $50 | 2/6/21 |
| RDO 2 | SM 2 | LOC 1 | LOB1 | Sally Jones | 234567 | Active | $75 | 2/10/21 |
| RDO 2 | SM 1 | LOC 2 | LOB2 | Sally Jones | 234567 | Active | $45 | 2/11/21 |
I need to calculate average weekly revenue by LOB during the timeframe selected in the date slicer.
I have this working beautifully, but I need to make a change, and I can't figure out how to do it.
To calculate the averages, I created measures for the following:
AVG NUMERATOR = Sum(RevenueByTech[Revenue])
Number of Distinct Techs =
CALCULATE (
DISTINCTCOUNT( RevenueByTech[TechLogin] ))
No of Weeks During Slicer Dates =
VAR _startDate = min( 'DATE Dim'[DATE] )
VAR _endDate = max( 'DATE Dim'[DATE] )
RETURN
AVERAGEX (
VALUES ( RevenueByTech[TechLogin] ),
DATEDIFF ( _startDate, _endDate, WEEK )
)
Average Weekly Revenue =
DIVIDE ( RevenueByTech[AVG NUMERATOR] , RevenueByTech[Number of Distinct Techs] * RevenueByTech[No of Weeks During Slicer Dates])
Using the above formula allowed the average calculation to change when different LOBs and orgs were selected from the slicer. The formula would only count the distinct techs that had revenue during the time period and based on the parameters selected in the other slicers (Org, LOB).
Now I need to use the same count of distinct techs for all LOBs in the average calculation. So I need to count the number of distinct techs during the slicer dates, and use that in the denominator. How do I calculate the number of distinct dates such that it doesn't change with the LOB selected?
