Table VALUES(Table) with count of Occurence of each item.

[HankScorpio2]

Hi

I have created a table with Values on a column of text values which are the agent statuses when logging in and out. 

Would like to create a count of how many times each status occurred. 

Statuses = VALUES('Agent Status Summary'[Status])

This creates the table and returns as expect the unique strings. 

But how do I get the count of each occurence of each value in the next column.

Currently it returns the count of all.

I have tried ifferent variants of formulas like 

Count = CALCULATE(COUNTROWS('Agent Status Summary'),ALLEXCEPT('Agent Status Summary','Agent Status Summary'[Status]))

Count = COUNTX(VALUES('Agent Status Summary'), [Status])

[danextian · Accepted solution]

Hi @HankScorpio2 ,

VALUES returns the distinct values of a referenced column in a calculated table but doesn't aggregate. Try this instead:

Summary =
ADDCOLUMNS (
    SUMMARIZE ( Data, Category[Category] ),
    "Count", CALCULATE ( COUNTROWS ( Data ) )
)

[v-yiruan-msft · Accepted solution]

@danextian Thanks for your contribution on this thread.

Hi @HankScorpio2 ,

You can follow the steps belwo to get it:

1. Create a measure as below:

Count =
VAR _status =
    SELECTEDVALUE ( 'Agent Status Summary'[Status] )
RETURN
    COUNTROWS (
        FILTER ( 'Agent Status Summary', 'Agent Status Summary'[Status] = _status )
    )

2. Create a table visual: drag the field 'Agent Status Summary'[Status] and the above new measure onto the visual

Best Regards

[danextian · Accepted solution]

Hi @HankScorpio2 ,

VALUES returns the distinct values of a referenced column in a calculated table but doesn't aggregate. Try this instead:

Summary =
ADDCOLUMNS (
    SUMMARIZE ( Data, Category[Category] ),
    "Count", CALCULATE ( COUNTROWS ( Data ) )
)

[HankScorpio2]

Hi what does Data represent in your model, a measure?

[danextian]

SUMMARIZE is a table function so it can be applied to a physical or virtual table only.