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.
Solved! Go to Solution.
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] ) ) ) )
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 ?
I have used the below measure
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.