CODEWITHSUNDEEP
powerbidax
t0rres
t0rres

Reputation: 211

Power BI : Average of Distinct count from a column

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

Upvotes: 1

Views: 9590

Answers (2)

t0rres
t0rres

Reputation: 211

Solution to my question here:

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

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40204

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] )
)

Upvotes: 1

Related Questions