Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vk_pbi
Resolver II
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. 

 

vk_pbi_0-1684645791859.png

 

 Thanks

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

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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1684649092375.png

 

Jihwan_Kim_1-1684649427349.png

 

 

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.


Go to My LinkedIn Page


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

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

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.


Go to My LinkedIn Page


Thanks @Jihwan_Kim 

I have used the below measure

CALCULATE(

      AVERAGEX(

                 VALUES('Date'[Month]),

                  [Total Sales]

        ),

         REMOVEFILTERS('Date'),

         VALUES('Date'[Year])

)

         

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors