Count Rows by Group

[kault]

I need to get the count of accounts by group within a table.

This is the data I have:

Table 1: Accounts

Owner	Account Name
Allison	Account A
Allison	Account B
Allison	Account C
Jim	Account D

I want to COUNT the number of rows per OWNER, to get the following:

Owner	Count of Accounts
Allison	3
Jim	1

How do I achieve this?

[VahidDM · Accepted solution]

@kault 

Hmmmm, fo this request, first make a relationship between those two tables

then use the below measure:

percent of Recent by all account =
VAR _Total_Acc =
    COUNT ( 'Table A'[Account Name] ) // COUNT the number of rows per OWNER
RETURN
    VAR _Recent_Activity =
        COUNT ( 'Table B'[Account Name] ) // # of accounts with recent activity 
    RETURN
        VAR _Percentage = _Recent_Activity / _Total_Acc
        RETURN
            IF ( OR ( _Percentage = 0, ISBLANK ( _Percentage ) ), 0, _Percentage )

The output will be as bellow:

If you want to find that value for a specific owner, use calculate measure and add that owner name in the filter.

IF this is not the output you are looking for, please share more details about using the percentage measure in your report.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[VahidDM]

Hi @kault 

To achive that output, follow the below items:

1- Add table to your report

2- Add Owner and Account Name to the table values:

3- Select the Count in the aggregate type of Account Name:

The output will be like the below image:

For more information about the work with aggregates click here.
If you want to use DAX, use below code:

No. Account = 
Count('Table'[Account Name])

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[kault]

Ah! I was unclear (I'm not very familiar with terminology yet). I do not need the visual. I want this to be a data value, so that I can use it in a measure.

Ultimately, I have two tables:

Table A is the accounts by owner:

Owner	Account Name
Allison	Account A
Allison	Account B
Allison	Account C
Jim	Account D

I want to COUNT the number of rows per OWNER, to get the following:

Owner	Count of Accounts
Allison	3
Jim	1

Table B is the # of accounts with recent activity by person

Owner	Account Name
Allison	Account A
Allison	Account C

I want to COUNT the number of rows per OWNER to get the follwing:

Owner	Count of Accounts
Allison	2
Jim	0

So that I can ultimately create a measure to get the percent of (# of accounts with recent activity by owner) divided by (# of accounts owned by owner). In other words, I want to see Allison is at (2/3) and Jim is at (0/1)

[ryan_mayu · Accepted solution]

@kault 

you can create a dim owner table and create a measure

Measure = (DISTINCTCOUNT('Table B'[Account Name])+0)/DISTINCTCOUNT('Table A'[Account Name])

plse see the attachment below.

[VahidDM · Accepted solution]

@kault 

Hmmmm, fo this request, first make a relationship between those two tables

then use the below measure:

percent of Recent by all account =
VAR _Total_Acc =
    COUNT ( 'Table A'[Account Name] ) // COUNT the number of rows per OWNER
RETURN
    VAR _Recent_Activity =
        COUNT ( 'Table B'[Account Name] ) // # of accounts with recent activity 
    RETURN
        VAR _Percentage = _Recent_Activity / _Total_Acc
        RETURN
            IF ( OR ( _Percentage = 0, ISBLANK ( _Percentage ) ), 0, _Percentage )

The output will be as bellow:

If you want to find that value for a specific owner, use calculate measure and add that owner name in the filter.

IF this is not the output you are looking for, please share more details about using the percentage measure in your report.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!