cancel
Showing results for
Did you mean: Frequent Visitor

## average of the last 5 rows

 product type station time taken rank average A Z10 4 6 4 A Z10 2 5 3 = (2+4)/3 A Z10 3 4 3 = (3+2+4)/3 A Z10 2 3 4.5 = (2+3+2+4)/4 A Z10 3 2 4.75 = (5+3+2+3+4)/5 A Z10 5 1 3 = (5+3+2+3+2)/5

I need a table to generate the average column for the last 5 rows with respect to each row, I have already created a rank column which is according to the order of time it occurs. can anyone help me on this. thanks a lot!

1 ACCEPTED SOLUTION  Community Support

Hi @Stan_Lego ,

I found some mistakes in your expression:(If I understand wrong,pls correct me)

 product type station time taken rank average A Z10 4 6 4 A Z10 2 5 3 = (2+4)/3should be 3=(2+4)/2 A Z10 3 4 3 = (3+2+4)/3 A Z10 2 3 4.5 = (2+3+2+4)/4 result should be 11/4=2.75 A Z10 3 2 4.75 = (5+3+2+3+4)/5 should be(3+2+3+2+4)/5=2.8 A Z10 5 1 3 = (5+3+2+3+2)/5

Create a measure as below:

``````Measure =
VAR _maxrank =
CALCULATE ( MAX ( 'Table'[rank] ), ALL ( 'Table' ) )
VAR _count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[rank] >= MAX ( 'Table'[rank] )
&& 'Table'[rank] <= _maxrank
)
)
VAR _finalcount =
IF ( _count > 5, 5, _count )
VAR _sum =
CALCULATE (
SUM ( 'Table'[time taken] ),
FILTER (
ALL ( 'Table' ),
'Table'[rank] >= MAX ( 'Table'[rank] )
&& 'Table'[rank]
< MAX ( 'Table'[rank] ) + 5
)
)
RETURN
DIVIDE ( _sum, _finalcount )
``````

And you will see: For the related .pbix file,pls see attached.

Best Regards,
Kelly

3 REPLIES 3  Super User

@Stan_Lego , Try a new column like

AverageX(filter(Table, [product type] =earlier([product type]) && [Rank] >= earlier([Rank])),[time])

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User! Frequent Visitor

Hi @amitchandak , right now it is just averaging the entire dataset with the same product type and workstation but did not take the average of 5 time taken column at a time  Community Support

Hi @Stan_Lego ,

I found some mistakes in your expression:(If I understand wrong,pls correct me)

 product type station time taken rank average A Z10 4 6 4 A Z10 2 5 3 = (2+4)/3should be 3=(2+4)/2 A Z10 3 4 3 = (3+2+4)/3 A Z10 2 3 4.5 = (2+3+2+4)/4 result should be 11/4=2.75 A Z10 3 2 4.75 = (5+3+2+3+4)/5 should be(3+2+3+2+4)/5=2.8 A Z10 5 1 3 = (5+3+2+3+2)/5

Create a measure as below:

``````Measure =
VAR _maxrank =
CALCULATE ( MAX ( 'Table'[rank] ), ALL ( 'Table' ) )
VAR _count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[rank] >= MAX ( 'Table'[rank] )
&& 'Table'[rank] <= _maxrank
)
)
VAR _finalcount =
IF ( _count > 5, 5, _count )
VAR _sum =
CALCULATE (
SUM ( 'Table'[time taken] ),
FILTER (
ALL ( 'Table' ),
'Table'[rank] >= MAX ( 'Table'[rank] )
&& 'Table'[rank]
< MAX ( 'Table'[rank] ) + 5
)
)
RETURN
DIVIDE ( _sum, _finalcount )
``````

And you will see: For the related .pbix file,pls see attached.

Best Regards,
Kelly   