How to Create a Measure for AVerage Number of the count of a column

[all4ephraim]

Hi 

please i need help with getting the monthly Average of the count of row called FacilityID. i have a table with snapshot below, i need help creating a measure that would display the average Number of Facilities onboarded monthly. the second table show the Distinctcount of FacilityID onboarded monthly, i want to display the monthly average also.

[v-yuta-msft]

@all4ephraim ,

Firstly, create a calculate column to achieve distinctcount every month per year using DAX like pattern below:

Rows_Per_Month_Per_Year =
CALCULATE (
    DISTINCTCOUNT ( Table[FacilityID] ),
    ALLEXCEPT ( Table, Table[Year], Table[Month] )
)

Then create a measure:

Average_Value =
AVERAGE ( Table[Rows_Per_Month_Per_Year ] )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[all4ephraim]

I have tried this out but the result i was getting is not what am looking for, see how my table displays currently below.i want it to display the names and for every months count of Facility ID, i want to display that months average number.

Thanks