Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
torraroger
Contributor III
Contributor III

Average per category expression.

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'

DateUserWorkbook 
DesemberJohnPlanning sheet 
DesemberJohnPlanning sheet 
DesemberJohnPlanning sheet 
DesemberMarkPlanning sheet 
DesemberMarkPlanning 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!!!!!
Labels (3)
1 Reply
andoryuu
Creator III
Creator III

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)