Partial average - how to?

[tez]

Hi,

I have data for years and weeks (one value per year and week)

I want to create a line chart, which shows a line over all weeks for the last two years (2020 and 2019), and shows a third line with the average of all other years (2000-2018). How can this be done the easiest way?

Regards,

Thomas.

[tez]

@v-yingjl & @parry2k thank you for the recommendations. Looks good, I´ll implement this!

[v-yingjl · Accepted solution]

Hi @tez ,

Based on your description, you can create these measures:

2019 = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', 'Table'[date].[Year] = 2019 )
)

2020 = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', 'Table'[date].[Year] = 2020 )
)

Average = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[date].[Year] >= 2000
            && 'Table'[date].[Year] <= 2018
    )
)

 

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@tez try this measure

Avg Line = CALCUAGE ( AVERAGE ( Table[Value] ), CalendarTable[Year] < 2019 )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡