cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

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.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

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.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

Resolver II

Thanks @Jihwan_Kim

I have used the below measure

CALCULATE(

AVERAGEX(

VALUES('Date'[Month]),

[Total Sales]

),

REMOVEFILTERS('Date'),

VALUES('Date'[Year])

)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors