Dynamic Sum of Total Sales by Quarter Ranked from High to Low

[Anonymous]

I am trying to calculate a dynamic average for the highest 3 Total Sales Quarters averaged to use as a target, so in theory if a new quarter had the highest bookings then it would replace number 1 ranked and all others drop down by 1 position.

I have a table that looks like this:

I have Total Bookings

Total Bookings =

CALCULATE(

sum('fact'[Amount]),

'fact'[type]="Closed")    

I have my Quarterly Bookings Ranked 

Quarterly Bookings Ranked =

RANKX(All('Calendar Table'[Year Quarter]),

'fact'[Total Bookings])

so now what I want to do is a new measure that gives an average of the top 3. I have written this DAX but I get an error

Average Bookings Top 3 Ranked =

CALCULATE(

'fact'[Total Bookings] / 3,

'fact'[Quarterly Bookings Ranked] = "1"||"2"||"3")

so essentially calculates the Total Bookings where Rank = 1 or 2 or 3 and divides by 3 to give an average.

I get this error

Any help with this would be great

[amitchandak]

@Anonymous , Try one of the two

CALCULATE(AverageX(values('Calendar Table'[Year Quarter]),[Total Bookings]) ,TOPN(3,allselected('Calendar Table'[Year Quarter]),[Total Bookings],DESC),VALUES('Calendar Table'[Year Quarter]))


CALCULATE(AverageX(values('Calendar Table'[Year Quarter]),[Total Bookings]) ,TOPN(3,allselected('Calendar Table'[Year Quarter]),[Total Bookings],DESC))