cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver II

## Yearly Average straight line in Column chart

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

1 ACCEPTED SOLUTION
Super User

Hi,

``````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:]
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

4 REPLIES 4
Super User

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] )
)
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Resolver II

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

Super User

Hi,

``````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:]
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Resolver II

Thanks @Jihwan_Kim

I have used the below measure

CALCULATE(

AVERAGEX(

VALUES('Date'[Month]),

[Total Sales]

),

REMOVEFILTERS('Date'),

VALUES('Date'[Year])

)