Average of certain Years

[Anonymous]

Hi,

I have this table

I need a Calculated Column in another table wich has Unique Branch values. This Column needs to show the average of the last 2 yeas ( Average of the SUM of score for each team based on year 3 and 4)

Thanks for your help!

[v-zhenbw-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculate column to meet your requirement.

Column = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Branch] = 'Table (2)'[Branch] )
    )
VAR _min = _max - 1
VAR _sum_max =
    CALCULATE (
        SUM ( 'Table'[Score] ),
        FILTER (
            'Table',
            'Table'[Year] = _max
                && 'Table'[Branch] = 'Table (2)'[Branch]
        )
    )
VAR _sum_min =
    CALCULATE (
        SUM ( 'Table'[Score] ),
        FILTER (
            'Table',
            'Table'[Year] = _min
                && 'Table'[Branch] = 'Table (2)'[Branch]
        )
    )
RETURN
    DIVIDE ( _sum_max + _sum_min, 2 )

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

please try this

newtable = 
VAR maxyear=max('Table (2)'[year])
return SUMMARIZE(FILTER('Table (2)','Table (2)'[year]>=maxyear-1),'Table (2)'[branch],"AVG",AVERAGE('Table (2)'[score]))

[Ashish_Mathur]

Hi,

Why do you need a calculated column formula?  Why not a measure in a Table visual?