Average per category expression.

[torraroger]

Hi all,

I want to build a chart where the height of the columns are the COUNT OF WORKBOOK,  AVERAGE PER USER: 

The structure of the data table is the followiing: 

Table: 'Query Detail - Workbook and Worksheet'

Date	User	Workbook
Desember	John	Planning sheet
Desember	John	Planning sheet
Desember	John	Planning sheet
Desember	Mark	Planning sheet
Desember	Mark	Planning sheet

With this table, I want to build a graph with dimension "month" and with measure "Count of workbook, average per user". In this case, the result of the measure for desember would be 2.5. 

In powe BI(DAX), the expression is the following: 

Count of Workbook average per User =

AVERAGEX(

    KEEPFILTERS(VALUES('Query Detail - Workbook and Worksheet'[User])),

    CALCULATE(COUNTA('Query Detail - Workbook and Worksheet'[Workbook]))

)

Thanks!!!!!

[andoryuu]

I would add a key to this table using RowNo() as PK or something like that in the data load and then do the following:
Count(Distinct [User]) / Count(PK)