Yearly Average straight line in Column chart

[vk_pbi]

Hi,

I am trying to write a measure which can calculate the yearly average and show it as straight line in stacked column chart. The measure works fine if my chart granularity is at year, if I moved it to months-year (format Jan-22), then it calculates average for each month, not giving a straight line. 

The requirement is to calculate the Yr Avg column. 

 Thanks

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but 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.

Avg. expected outcome: =
IF (
    NOT ISBLANK ( [Values measure:] ),
    CALCULATE (
        AVERAGEX ( VALUES ( 'Calendar'[Month-Year] ), [Values measure:] ),
        WINDOW (
            1,
            ABS,
            -1,
            ABS,
            ALL ( 'Calendar'[Year], 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
            ORDERBY ( 'Calendar'[Month-Year sort], ASC ),
            KEEP,
            PARTITIONBY ( 'Calendar'[Year] )
        )
    )
)

[vk_pbi]

Hi @Jihwan_Kim ,

Thanks for your quick reply. As I am using older version of power BI report server (ver May 2021), in which WINDOW function is not available.
Could you plesae suggest me alternate solution ?

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below.

Avg. expected outcome V2: =
IF (
    NOT ISBLANK ( [Values measure:] ),
    AVERAGEX (
        FILTER (
            ALL ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort], 'Calendar'[Year] ),
            'Calendar'[Year] = MAX ( 'Calendar'[Year] )
        ),
        [Values measure:]
    )
)

[vk_pbi]

Thanks @Jihwan_Kim 

I have used the below measure

CALCULATE(

      AVERAGEX(

                 VALUES('Date'[Month]),

                  [Total Sales]

        ),

         REMOVEFILTERS('Date'),

         VALUES('Date'[Year])

)