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
pbix1
Resolver I
Resolver I

Grouped Rolling Average

Hi

 

I have the following m code to create a grouped running average:

 

try

let Group=[Route_DU_Key], Row=[Index] in
List.Average(Table.SelectRows(#"Added Index", each [Index]<=Row and [Route_DU_Key]=Group)[Counter])

otherwise 0

 

And the following m code to create a 13 period rolling average:

 

try List.Average(List.Range(#"Added Index"[Counter],([Index]-13),13)) otherwise 0

 

I want to create a grouped, 13 period rolling average, but am going round and round and keep getting errors when I try to combine them. Can anyone help with this? Thanks.

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @pbix1 

This M code creates a rolling average based on the sample data you supplied.  It does not need an Index column to work.

It will work with any number of 'Keys'.

You can download a sample PBIX file here

grouped-rolling-average.png

 

let

GRAList = (values as list, grouping as list) as list =>

  let

    TheList = List.Generate
    ( 
        ()=> [ GRT = values{0}, GRA = values{0}, i = 0, j = 1 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} then [GRT = [GRT] + values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = [j] + 1]

                 else [GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = 1]

             otherwise [i = [i] + 1],

        each [GRA]
    )
in
    TheList,


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),

    BufferedValues = List.Buffer(#"Changed Type"[Counter]),
    BufferedGrouping = List.Buffer(#"Changed Type"[Key]),

    GroupedRunningAverage = Table.FromList(GRAList(BufferedValues, BufferedGrouping), Splitter.SplitByNothing(), {"GRA"}, null, ExtraValues.Error),
    Columns = List.Combine({Table.ToColumns(#"Changed Type"),Table.ToColumns(GroupedRunningAverage)}),
    #"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Changed Type"),{"Rolling Avg"}})),
    #"Reordered Columns" = Table.ReorderColumns(#"Converted to Table",{"Key", "Index", "Counter", "Rolling Avg", "MAA"})
in
    #"Reordered Columns"

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Hi. Thanks for all the responses.  Phil, I tried your method but I could only see how it worked for a rolling average which I had already had a method for. I was after a rolling average by group. I found this method, which after a bit of adapting, worked for what I needed. Link below in case it helps anyone else:

 

https://stackoverflow.com/questions/55337952/power-query-m-language-50-day-moving-average

 

Finally! found a solution. First, apply Index by product see this post for further details Then index again without criteria (index all rows) Then, apply below code
= Table.AddColumn(#"Previous Step", "Volume SMA(50)", each if [Index_byProduct] >= 50 then List.Average(List.Range(#"Previous Step"[Volume], ([Index_All]-50),50)) else 0),
For large dataset, Table.Buffer function is recommended after index-expand step to improve PQ calculation speed

View solution in original post

7 REPLIES 7
PhilipTreacy
Super User
Super User

Hi @pbix1 

This M code creates a rolling average based on the sample data you supplied.  It does not need an Index column to work.

It will work with any number of 'Keys'.

You can download a sample PBIX file here

grouped-rolling-average.png

 

let

GRAList = (values as list, grouping as list) as list =>

  let

    TheList = List.Generate
    ( 
        ()=> [ GRT = values{0}, GRA = values{0}, i = 0, j = 1 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} then [GRT = [GRT] + values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = [j] + 1]

                 else [GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = 1]

             otherwise [i = [i] + 1],

        each [GRA]
    )
in
    TheList,


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),

    BufferedValues = List.Buffer(#"Changed Type"[Counter]),
    BufferedGrouping = List.Buffer(#"Changed Type"[Key]),

    GroupedRunningAverage = Table.FromList(GRAList(BufferedValues, BufferedGrouping), Splitter.SplitByNothing(), {"GRA"}, null, ExtraValues.Error),
    Columns = List.Combine({Table.ToColumns(#"Changed Type"),Table.ToColumns(GroupedRunningAverage)}),
    #"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Changed Type"),{"Rolling Avg"}})),
    #"Reordered Columns" = Table.ReorderColumns(#"Converted to Table",{"Key", "Index", "Counter", "Rolling Avg", "MAA"})
in
    #"Reordered Columns"

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi. Thanks for all the responses.  Phil, I tried your method but I could only see how it worked for a rolling average which I had already had a method for. I was after a rolling average by group. I found this method, which after a bit of adapting, worked for what I needed. Link below in case it helps anyone else:

 

https://stackoverflow.com/questions/55337952/power-query-m-language-50-day-moving-average

 

Finally! found a solution. First, apply Index by product see this post for further details Then index again without criteria (index all rows) Then, apply below code
= Table.AddColumn(#"Previous Step", "Volume SMA(50)", each if [Index_byProduct] >= 50 then List.Average(List.Range(#"Previous Step"[Volume], ([Index_All]-50),50)) else 0),
For large dataset, Table.Buffer function is recommended after index-expand step to improve PQ calculation speed

Mariusz
Community Champion
Community Champion

Hi @pbix1 

 

Can you create a sample data and add the results that you expecting?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 Hi Mariusz

 

I'm trying to create a moving average that resets at each change of 'Key'. Where it produces an error because there are not enough periods to create it, this can show as zero. I have based this on a 2 period rolling average but would like the flexibility to change the number of periods.

 

Key                    Index                Counter            MAA

Route 01           1                       12                      0
Route 01           2                        6                       9
Route 01           3                        8                       7
Route 02           4                        4                       0
Route 02           5                        16                     10
Route 02           6                        10                     13

 

Does this help? Many thanks.

 

Neil

Here is a column expression that gets your desired results (although it does not give zero for the first value of a Key).

 

MAA =
VAR thisindex = Counter[Index]
VAR lasttwoindex =
    TOPN (
        2,
        CALCULATETABLE (
            VALUES ( Counter[Index] ),
            ALLEXCEPT (
                Counter,
                Counter[Key]
            ),
            Counter[Index] <= thisindex
        ),
        Counter[Index], DESC
    )
RETURN
    CALCULATE (
        AVERAGE ( Counter[Counter] ),
        ALLEXCEPT (
            Counter,
            Counter[Key]
        ),
        lasttwoindex
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi Pat

 

Thanks for your reply. That looks like DAX though. I'm after a solution in m code to use in Power Query. I'm not really comfortable with DAX, and as an MS Access user I find Power Query a lot easier to understand.

 

Thanks - Neil 

Mariusz
Community Champion
Community Champion

Hi @pbix1 

 

Please see the below, however, DAX calculation would probably be a better solution for this sort of manipulations.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"rows", each _, type table [Key=nullable text, Index=nullable number, Counter=nullable number, MAA=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each [rows][Index], type list),
    #"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each 
        let 
            lesThanIndex = Table.SelectRows( [rows], (a) =>  a[Index] <= [Index] ),
            sort = Table.Sort(lesThanIndex,{{"Index", Order.Descending}}),
            namberOfPeriods = 2,
            FirstN =  Table.FirstN( sort, namberOfPeriods )[Counter]
        in
            if List.NonNullCount(  FirstN ) = namberOfPeriods then List.Average( FirstN ) else 0 
    )
in
    #"Added Custom1"

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

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.

Top Solution Authors
Top Kudoed Authors