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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

 


@ 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.

 


@ 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.


@ 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

 


@ 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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.