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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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