Dax for average group size measure

[arb1782]

Hi

I've just modelled some data so that I have each row is an individual who has attending a health group. I want to create a measure that shows the average number of group participants.

My data is structured like this:

Group ID	Date of group	Group name	Person ID
1	12/09/20	Health group	A
1	12/09/20	Health group	B
1	12/09/20	Health group	C
2	13/09/20	Disability group	D
2	13/09/20	Disability group	E
3	14/09/20	Exercise group	A
3	14/09/20	Exercise group	C
3	14/09/20	Exercise group	G
3	14/09/20	Exercise group	H

What would be the DAX for this?

Thanks

arb

[amitchandak]

@arb1782 , Try like

AvergaeX(Values(Table[Group ID]), count(Table[Person ID]))

or

AvergaeX(summarize(Table[Group ID],"_1", count(Table[Person ID])),[_1])

[arb1782]

Hi

The answer from the sample data should be 3. So far the table and the two formulae provided don't solve my issue.

Thanks for responses so far.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=countrows(Data)/distinctcount(Data[Group ID])

Hope this helps.

[arb1782]

Thanks all

[arb1782]

Any other ideas @amitchandak  @VijayP  @negi007  ?

[v-yiruan-msft]

Hi @arb1782 ,

You can create a measure as below:

Measure =
VAR _countofallp =
    CALCULATE ( COUNT ( 'Table'[Person ID] ), ALL ( 'Table' ) )
VAR _countofgroup =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Group ID] ) )
RETURN
    DIVIDE ( _countofallp, _countofgroup, 0 )​​​

Best Regards

Rena

[arb1782]

Thanks @v-yiruan-msft 

I get the following error: "The following syntax error occurred during parsing: Invalid token, Line 7, Offset 46, ." The little red squiggle is after the final bracket of the DIVIDE.

Any idea how to resolve this?

[v-yiruan-msft]

Hi @arb1782 ,

Sorry, I made a mistake... The error message is caused by the wrong use of the last bracket in the formula, please update the formula of measure as below:

Measure =
VAR _countofallp =
CALCULATE ( COUNT ( 'Table'[Person ID] ), ALL ( 'Table' ) )
VAR _countofgroup =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Group ID] ) )
RETURN
DIVIDE ( _countofallp, _countofgroup, 0 )

In addition, you can use DAX Formatter tool to check if there are any syntax errors in your formula and format it.

Best Regards

Rena

[VijayP]

@arb1782 

Do you want to have this as a result?

[negi007]

@arb1782 If you only wish to calculate the number of participant per group, there is no need to create a measure. it can be done simply using below approach. Put person ID in the values field and change the calculation to count from the dropdown icon