How to get average of monthly values for per year per category

[kk_shp_user]

My dataset looks like the below,

I have created a matrix visual as below,

The values in the matrix visuals are distinctcount of the ids.

Now, I want to calculate the average value of the red boxes (i.e.) the average of monthly values for each year for each category. How can I get this in DAX?

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected outcome measure: = 
AVERAGEX (
    WINDOW (
        1,
        ABS,
        12,
        ABS,
        SUMMARIZE (
            ALL ( 'Calendar' ),
            'Calendar'[Year],
            'Calendar'[Year-Month],
            'Calendar'[Year-Month sort]
        ),
        ORDERBY ( 'Calendar'[Year-Month sort], ASC ),
        ,
        PARTITIONBY ( 'Calendar'[Year] )
    ),
    CALCULATE ( COUNTROWS ( Data ) )
)

[kk_shp_user]

Thanks! This worked 🙂