Forum Discussion

hwoehler's avatar
hwoehler
Helper I
6 years ago
Solved

Get Value X Rows before/ every Xth row

Hello everybody, I would like to display the value of a special row (10th row) along with the last/most recent possible value to divide these two numbers. This should be done for each group (A, B et...
Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi hwoehler ,

    Try this:

    • Create a calculated column for the Index within a group.

     

     

    Index Per Group =
RANKX (
    FILTER (
        Performance130,
        Performance130[Group] = EARLIER ( Performance130[Group] )
    ),
    Performance130[Date],
    ,
    ASC,
    Dense) - 1
​a

     

     

    • Create this measure:

     

     

    Value =
VAR __10 =
    CALCULATE (
        SUM ( Performance130[Close] ),
        Performance130[Index Per Group] = 10
    )
VAR __0 =
    CALCULATE ( SUM ( Performance130[Close] ), Performance130[Index Per Group] = 0 )
RETURN
    DIVIDE ( __0, __10 )
​

     

     

    • Use Group column and Value measure in a visual.
danextian's avatar
danextian
Super User
6 years ago

Hi hwoehler ,

Try this:

  • Create a calculated column for the Index within a group.

 

 

Index Per Group =
RANKX (
    FILTER (
        Performance130,
        Performance130[Group] = EARLIER ( Performance130[Group] )
    ),
    Performance130[Date],
    ,
    ASC,
    Dense) - 1
​a

 

 

  • Create this measure:

 

 

Value =
VAR __10 =
    CALCULATE (
        SUM ( Performance130[Close] ),
        Performance130[Index Per Group] = 10
    )
VAR __0 =
    CALCULATE ( SUM ( Performance130[Close] ), Performance130[Index Per Group] = 0 )
RETURN
    DIVIDE ( __0, __10 )
​

 

 

  • Use Group column and Value measure in a visual.