Count/average based on another column

[safetyfirst]

I'm trying to calculate the number of inspector observations per report name. With this I want to calculate the average number of observations per name at inspection. Is this possible?

Report Name	Name at Inspection	Inspector Observations
XXXX-XXXX-XXXX-XXXX	Liberty	A
XXXX-XXXX-XXXX-XXXX	Liberty	D
XXXX-XXXX-XXXX-XXXY	Freedom	A
XXXX-XXXX-XXXX-XXXY	Freedom	B
XXXX-XXXX-XXXX-XXXY	Freedom	C
XXXX-XXXX-XXXX-XXXZ	Liberty	B
XXXX-XXXX-XXXX-XXXZ	Liberty	C
XXXX-XXXX-XXXX-XXXZ	Liberty	D

[v-huizhn-msft]

Hi @safetyfirst,

>>I'm trying to calculate the number of inspector observations per report name. 

You can create a table visual, select the report name and count of Inspector Observations as values, or you can create a new table by clicking "New Table" under Modeling on Homepage. Please review the picture1 and picture2.

Table = SUMMARIZE(Table2,Table2[Report Name],"the number of inspector observations",COUNTA(Table2[Inspector Observations]))

picture1picture2
>>I want to calculate the average number of observations per name at inspection.

You use the same way as above, select Average of Inspector Observations, or create a new table as follows.

Measure 2 = DIVIDE(DISTINCTCOUNT(Table2[Inspector Observations]),COUNTA(Table2[Name at Inspection]))

picture3

Create a new table.

Table 2 =
SUMMARIZE (
    Table2,
    Table2[Name at Inspection],
    "Average of Inspector Observations", DIVIDE (
        DISTINCTCOUNT ( Table2[Inspector Observations] ),
        COUNTA ( Table2[Name at Inspection] )
    )
)

Please let me know if this is not what you want.

Best Regards,
Angelia

[safetyfirst]

Thank you @v-huizhn-msft! I believe this is just what I'm looking for, but it is calculating the averages as 1 using both methods.

Any idea why this may be? Some of the reports do have the same observation if this affects the calculation.

Thanks for your help.

[v-huizhn-msft]

Hi @safetyfirst,

Could you please mark the reply as answer if you think it's useful, so more people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @safetyfirst,

>>but it is calculating the averages as 1 using both methods

Actually, I can't understand your problem, could you please describe it in details?

Best Regards,
Angelia

[Greg_Deckler]

So what are your expected results from the example data?