# Power BI: Average of a DistinctCount

• ### Question

• I have the following tables:

FactAssign { FactKey, BranchID, ClientID, CustomerName, StartDate, CalendarWeekKey, EmployeeguId }

DimBranch { BranchID, BranchName, Region}

DimClient { clientID, ClientName }

DimCalendar { CalendarWeekKey, WeekEndingDate, CalendarYear, CalendarWeek }

Data from FactAssign table here

Result I need

Here <g class="gr_ gr_28 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="28" id="28">CurrentWeek</g> 50 is the Average of the distinct count of Employees per day for branchID 4 for this week. Distinct Counts of Employees this week are 56,53,48,47,46 respectively from Monday thru Friday.

How can I get the AVERAGE of the DISTINCTCOUNT of Employees per branch per Week?

Dax I used :

Averagex = CALCULATE(AVERAGEX( VALUES(TestingAverageX[CalendarWeekKey]), DISTINCTCOUNT(TestingAverageX[EmployeeGUID])), FILTER(TestingAverageX, TestingAverageX[CalendarWeekKey] = 20190303))

Regards, Success

Regards,
Success

• Edited by Sunday, March 3, 2019 7:13 AM updates question with more details
Friday, March 1, 2019 10:44 AM

• Ah, sorry, you'll need to wrap the distinct count in an calculate to force the calculation of the daily distinct count (otherwise it's just calculating the weekly distinct count 7 times)

Averagex = AVERAGEX(
VALUES(TestingAverageX[StartDate])
, CALCULATE( DISTINCTCOUNT(TestingAverageX[EmployeeGUID]) )
)

Sunday, March 3, 2019 7:46 AM

### All replies

• The problem is that you are doing an averagex at the week level and it looks like you actually want to average the daily counts. So swapping the column that you are iterating over should fix this.

eg.

Averagex = AVERAGEX(
VALUES(TestingAverageX[StartDate])
, DISTINCTCOUNT(TestingAverageX[EmployeeGUID])
)

Friday, March 1, 2019 10:07 PM
• Hey Darren,

thanks for your reply. I had tried this before as well. IT doesn't change the output when I put the weekkey in the visual as I need a weekly average of the distinct count of Employees per day per branch.

Regards,

Success

Sunday, March 3, 2019 6:55 AM
• Ah, sorry, you'll need to wrap the distinct count in an calculate to force the calculation of the daily distinct count (otherwise it's just calculating the weekly distinct count 7 times)

Averagex = AVERAGEX(
VALUES(TestingAverageX[StartDate])
, CALCULATE( DISTINCTCOUNT(TestingAverageX[EmployeeGUID]) )
)