cancel
Showing results 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

Helper II

## Showing Detail Rows of Rolling Average

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

 Period Value 01 1 02 4 03 2 04 3 05 6 06 3 07 1 08 1

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

 Period Rolling Average 01 0 02 0 03 0 04 2.5 05 3.75 06 3.5 07 3.25 08 2.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
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...