Creatiing a new measure

[treyd]

Hi, I need help creating a new measure and have searched but can't find the answer.  I'm trying to do a count of the rows and return the average by each group.

For example, given this data set:

Column A	Column B
Group 1	Set 1
Group 1	Set 1
Group 1	Set 2
Group 2	Set 3
Group 2	Set 3
Group 3	Set 4
Group 1	Set 5
Group 1	Set 5
Group 2	Set 6

I would expect to see this result:

Column A	Avg Count
Group 1	1.666
Group 2	1.5
Group 3	1

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result: =
DIVIDE ( COUNTROWS ( Data ), COUNTROWS ( DISTINCT ( Data[Column B] ) ) )

[FreemanZ · Accepted solution]

hi @treyd ,

Try to plot a table measure with ColumnA and a measure like:

AvgCount = 
VAR _table =
ADDCOLUMNS(
    VALUES(data[ColumnB]),
    "Count",
    CALCULATE(COUNTROWS(data))
)
RETURN
AVERAGEX(_table, [Count])