Power BI : Average of Distinct count from a column

1
votes

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

Sample rows:

| BranchID | ClientID | StartDate | CalendarWeekKey | EmployeeGUID | DayofWeek |
|----------|----------|-----------|-----------------|--------------|-----------|
| 4        | 591      | 3/1/2019  | 20190303        | 783357       | Friday    |
| 4        | 591      | 3/1/2019  | 20190303        | 3744071      | Friday    |
| 4        | 591      | 3/1/2019  | 20190303        | 710020       | Friday    |
| 4        | 591      | 3/1/2019  | 20190303        | 754929       | Friday    |
| 4        | 3032     | 3/1/2019  | 20190303        | 4036981      | Friday    |
| 4        | 5192     | 3/1/2019  | 20190303        | 731638       | Friday    |
| 4        | 5192     | 3/1/2019  | 20190303        | 784118       | Friday    |
| 4        | 5790     | 3/1/2019  | 20190303        | 756802       | Friday    |
| 4        | 5790     | 3/1/2019  | 20190303        | 3748444      | Friday    |
....

Result I need enter image description here

Here CurrentWeek 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

2
powerbidax
Your post is not specific enough to answer this without making a bunch of guesses. Please edit the post to include the information in your comment below as well as an example of how your data table(s) are structured. – Alexis Olson
added more details to the question. I think my question is clear now. Can you please review? @Alexis olson – Success Maharjan

2 Answers

1
votes

Solution to my question here:

AverageX =  CALCULATE (
        AVERAGEX (
            VALUES ( TestingAverageX[StartDate] ),
            CALCULATE ( DISTINCTCOUNT ( TestingAverageX[EmployeeGUID] ) )
        )
0
votes

I'm not sure exactly what filter context you want the measure to be evaluated in, but try something along these lines:

AVERAGEX(
    VALUES( Table1[BranchName] ),
    DISTINCTCOUNT( Table1[EmployeeID] )
)