cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hwoehler Helper I
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):
ExcelExampleP30.PNGThe 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
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

1 REPLY 1
Highlighted
Super User I
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors