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
WZorn
Helper II
Helper II

Showing Detail Rows of Rolling Average

I have a single data table that has two columns: Period and Value.

 

PeriodValue
011
024
032
043
056
063
071
081

 

The first visual is a table that displays a measure that calculates a 4 period rolling average of the value column.

 

PeriodRolling Average
010
020
030
042.5
053.75
063.5
073.25
082.75

 

 

I would like to have a second table visual that displays the 4 rows that make up the rolling average when one of the rows from the first visual is selected.  For example:  If Period 06 is selected in the first visual, the second visual should show the row data for periods 03, 04, 05, and 06.

 

I can't figure out how to filter the second visual to only those 4 rows.

 

Any help would be greatly appriciated.  I've googled and can't quite find what I'm looking for.

 

 

 

1 ACCEPTED SOLUTION

@WZorn Yep, got it. You just need to create an alternate Rolling Average measure for use in your second table. Updated PBIX is attached.

 

Rolling Average Alt = 
    VAR __Period = MAX('Periods'[Period]) * 1
    VAR __Table = ADDCOLUMNS( ALL('Table'), "__Period", [Period] * 1 )
    VAR __Rows = FILTER( __Table, [__Period] <= __Period && [__Period] >= __Period - 3 )
    VAR __Result = IF( COUNTROWS( __Rows) >= 4, AVERAGEX(__Rows, [Value]), 0)
RETURN
    __Result

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@WZorn You essentially want what I refer to as a Complex Selector. This would be easier if you had an Index or you made a duplicate of your Period (assume it is text given the leading zero). The Complex Selector - Microsoft Fabric Community. In any even, probably something along these lines. However, the real trick in your case is to ensure that selecting a Period in the first visual does not prematurely filter the second visual so that will likely need a disconnected table. Let me take a quick look and see if I can get a working PBIX file going.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I've read the link you provided.  Yes, I've started following that exact path.  I've even disconnected the second table visual from the first.  The problem is that I haven't figured out how to "pass" the selected period from the first visual to the second.  I feel like the only way to do it is to have a second data table.  I was trying to avoid duplicating data.  I'm curious to see what you come up with.

@WZorn Sorry, got sidetracked yesterday. Here is an example PBIX (attached below signature). See what you think.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Part of the way there.  It gives the 4 underlying periods that make up the rolling average in the first table, but it dosen't give the values for those 4 periods.  When you add the value field, it duplicates the value in the final period across all 4 periods.

 

In this, I've selected Period 05:

WZorn_0-1706713568559.png

Here is the underlying data.  Period 05 has a value of 6.

WZorn_1-1706713631622.png

 

@WZorn Yep, got it. You just need to create an alternate Rolling Average measure for use in your second table. Updated PBIX is attached.

 

Rolling Average Alt = 
    VAR __Period = MAX('Periods'[Period]) * 1
    VAR __Table = ADDCOLUMNS( ALL('Table'), "__Period", [Period] * 1 )
    VAR __Rows = FILTER( __Table, [__Period] <= __Period && [__Period] >= __Period - 3 )
    VAR __Result = IF( COUNTROWS( __Rows) >= 4, AVERAGEX(__Rows, [Value]), 0)
RETURN
    __Result

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors