Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

1 REPLY 1
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors