Grabbing Averages by month over years

[ale_spruce34]

Hello,

I currently have this table on excel:

I am trying to recreate this on PowerBI but am having some difficulty figuring it out. I am trying to grab the average for each month but over years. I thought it would be a simple AVG[ReturnQty] but the ReturnQty is not summed, it is added when putting it in a chart. 

I am trying something like this but the distinct count for month num would always just be 1, how can I count the # of months of years? 

Measure 2 = SUM([ReturnQty])/DISTINCTCOUNT([MonthNum])

[v-binbinyu-msft]

Hi @ale_spruce34 ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Measure =IF(HASONEVALUE('Table'[Month]),MAX('Table'[2015 Returns]),AVERAGEA('Table'[2015 Returns]))

Measure 2 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2016 Returns]),AVERAGEA('Table'[2016 Returns]))

Measure 3 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2017 Returns]),AVERAGEA('Table'[2017 Returns]))

Measure 4 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2018 Returns]),AVERAGEA('Table'[2018 Returns]))

Measure 5 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2019 Returns]),AVERAGEA('Table'[2019 Returns]))

Measure 6 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2020 Returns]),AVERAGEA('Table'[2020 Returns]))

Measure 7 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2021 Returns]),AVERAGEA('Table'[2021 Returns]))

Measure 8 = IF(HASONEVALUE('Table'[Month]),MAX('Table'[2022 Returns]),AVERAGEA('Table'[2022 Returns]))

Avg =
VAR cur_month =
    SELECTEDVALUE ( 'Table'[Month] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Month] = cur_month )
VAR _val =
    SUMX (
        tmp,
        [Measure] + [Measure 2] + [Measure 3] + [Measure 4] + [Measure 5] + [Measure 6] + [Measure 7] + [Measure 8]
    )
VAR _a =
    IF ( CALCULATE ( ISBLANK ( [Measure] ), tmp ), 1 )
VAR _b =
    IF ( CALCULATE ( ISBLANK ( [Measure 2] ), tmp ), 1 )
VAR _c =
    IF ( CALCULATE ( ISBLANK ( [Measure 3] ), tmp ), 1 )
VAR _d =
    IF ( CALCULATE ( ISBLANK ( [Measure 4] ), tmp ), 1 )
VAR _e =
    IF ( CALCULATE ( ISBLANK ( [Measure 5] ), tmp ), 1 )
VAR _f =
    IF ( CALCULATE ( ISBLANK ( [Measure 6] ), tmp ), 1 )
VAR _g =
    IF ( CALCULATE ( ISBLANK ( [Measure 7] ), tmp ), 1 )
VAR _h =
    IF ( CALCULATE ( ISBLANK ( [Measure 8] ), tmp ), 1 )
VAR _ctn = _a + _b + _c + _d + _e + _f + _g + _h
RETURN
    DIVIDE ( _val, 8 - _ctn )

Avg Per Month = IF(HASONEVALUE('Table'[Month]),[Avg],SUMX(ALL('Table'),[Avg])/12)

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.