cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

4 weeks average based on weekrank

Hi, 

I would like to calculate the 4 weeks average based on weekrank.
example:
Weekno     rank     value
20              1           10

19               2           15

18               3           10

17               4            9

20              5             5


Weekno and rank comes from the datetable and value is calculated measure.
My desired outcome for weekno 20  4weeks average= 10+15+10+9 = 11 etc....

I tried the following the measure but it does not show me the average:
4 weeks average = var _ranktable = calculatedtable(values(rank);filter(allselected(rank);rank<=max(rank)+3 && rank>=max(rank)))
return
averagex =(_ranktable, value)

Any suggestions? 



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: 4 weeks average based on weekrank

Hi @Coan7 

I build two table to have a test.

Date Table:

Weeknom and Rank columns are calculated columns.

 

Weeknom = WEEKNUM('Date'[Date],2)
Rank = RANKX('Date','Date'[Date],,DESC,Dense)

 

Result:

1.png

Value Table:

2.png

Build Value measure in Date table:

Value = CALCULATE(SUM('Value'[Value]),FILTER('Value','Value'[Date]=MAX('Date'[Date])))

Then I build a measure to achieve your goal.

 

4 weeks average =

VAR _Totalvalue= SUMX (

        FILTER (

            ALL ( 'Date' ),

            'Date'[Rank]

                <= MAX ( 'Date'[Rank] ) + 3

                && 'Date'[Rank] >= MAX ( 'Date'[Rank] )

        ),

        [Value]

    )

return

IF (

    MAXX ( ALL ( 'Date' ), 'Date'[Rank] ) - SUM ( 'Date'[Rank] ) >= 3,

    DIVIDE(_Totalvalue,4),

   BLANK()

)

 

Result:

3.png

You can download the pbix file from this link: 4 weeks average based on weekrank

 

Best Regards,

Rico Zhou

 

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: 4 weeks average based on weekrank

@Coan7 , refer, there is rolling formula

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper V
Helper V

Re: 4 weeks average based on weekrank

Tnx for your reply, but this does not seem to work for this case.
Highlighted
Super User IV
Super User IV

Re: 4 weeks average based on weekrank

@Coan7 , Try like


Last 4 Week = CALCULATE(Average(Table[value]),FILTER(all('Table'),'Table'[Rank]>=min('Table'[Rank])-4
&& 'Table'[Rank]<=max('Table'[Rank])))

Last 4 Week = CALCULATE(Average(Table[value]),FILTER(allselected('Table'),'Table'[Rank]>=min('Table'[Rank])-4
&& 'Table'[Rank]<=max('Table'[Rank])))

 

But prefer to have a week table and week and rank there

Like

 

Last 4 Week = CALCULATE(Average(Table[value]),FILTER(all('WEEK'),'WEEK'[Rank]>=min('WEEK'[Rank])-4
&& 'WEEK'[Rank]<=max('WEEK'[Rank])))

Last 4 Week = CALCULATE(Average(Table[value]),FILTER(allselected('WEEK'),'WEEK'[Rank]>=min('WEEK'[Rank])-4
&& 'WEEK'[Rank]<=max('WEEK'[Rank])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: 4 weeks average based on weekrank

Hi @Coan7 

I build two table to have a test.

Date Table:

Weeknom and Rank columns are calculated columns.

 

Weeknom = WEEKNUM('Date'[Date],2)
Rank = RANKX('Date','Date'[Date],,DESC,Dense)

 

Result:

1.png

Value Table:

2.png

Build Value measure in Date table:

Value = CALCULATE(SUM('Value'[Value]),FILTER('Value','Value'[Date]=MAX('Date'[Date])))

Then I build a measure to achieve your goal.

 

4 weeks average =

VAR _Totalvalue= SUMX (

        FILTER (

            ALL ( 'Date' ),

            'Date'[Rank]

                <= MAX ( 'Date'[Rank] ) + 3

                && 'Date'[Rank] >= MAX ( 'Date'[Rank] )

        ),

        [Value]

    )

return

IF (

    MAXX ( ALL ( 'Date' ), 'Date'[Rank] ) - SUM ( 'Date'[Rank] ) >= 3,

    DIVIDE(_Totalvalue,4),

   BLANK()

)

 

Result:

3.png

You can download the pbix file from this link: 4 weeks average based on weekrank

 

Best Regards,

Rico Zhou

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors