cancel
Showing results for
Did you mean:
Helper I

## 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 etc.). To illustrate the desired result, use the screenshot (generated in Excel):
The first three columns are already in the Power Bi file. I imagine maybe creating one index column per group, where the close value is taken for every 10th row (index = 10). (marked in color, it is always the 10th row) Then the last most recent close (index = 0) should be divided by the value at index = 10. Maybe someone have a solution for that? The Power BI file with the three first columns can be found here:
https://we.tl/t-28NZO8BN4Z

Thank you!

Kind Regards, hwoe

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

## Re: Get Value X Rows before/ every Xth row

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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User I

## Re: Get Value X Rows before/ every Xth row

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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors