Average of N rows - repeated - not moving average

[Pillic]

Hi Community,

I have a table like this with multiple WERKS and MATNR values.

Now I need to calculate the average of 3 rows followed by the next 3 rows and so on and display the result.

WERKS	LIFEX	Date	MATNR	fci	desired Measure
H703	703058765	16.05.2022	10089673	47,6	47
H703	703058772	16.05.2022	10089673	46,4
H703	703058673	12.05.2022	10089673	43,4
H703	703058683	12.05.2022	10089673	43,8	42,9
H703	703058596	10.05.2022	10089673	41,6
H703	703058552	10.05.2022	10089673	43,2
H703	703058557	10.05.2022	10089673	41,2	41,9
H703	703058568	10.05.2022	10089673	41,4
H703	703058407	04.05.2022	10089673	41
H703	703058423	04.05.2022	10089673	41,2	42,8
H703	703058417	04.05.2022	10089673	46,2
H703	703058269	22.04.2022	10089673	47,2
H703	703058055	13.04.2022	10089673	47,8	47,6
H703	703058038	13.04.2022	10089673	47,9

Is this possible as I dont have an index column? The LIFEX column has unique values but not in the right order (mixed up with other WERKS and MATNR)

[v-henryk-mstf · Accepted solution]

Hi @Pillic ,

Try like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc+7DcMwDATQXVjbAv+UNsgOhvZfw0qXnI04DcED+HDgcdCr2GijNTl6ZaxdWXXn2CVXEOY+st43Xi1pbmhUfplsfmPsyygYuzPxZDqYkQaGwcj1n4E9aKwpmnoyAqb+Mn41Hvlh2NHQnCc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [werks = _t, lidex = _t, date = _t, matnr = _t, fci = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"werks", type text}, {"lidex", Int64.Type}, {"date", type date}, {"matnr", Int64.Type}, {"fci", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Inserted Integer-Division" = Table.AddColumn(#"Inserted Modulo", "Integer-Division", each Number.IntegerDivide([Index], 3), Int64.Type)
in
    #"Inserted Integer-Division"

M = 
IF (
    MAX ( Test[Modulo] ) = 0,
    CALCULATE (
        SUM ( Test[fci] ),
        FILTER ( ALL ( Test ), Test[Integer-Division] = MAX ( Test[Integer-Division] ) )
    ) / 3,
    BLANK ()
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pillic]

@v-henryk-mstf 

Hello Henry,

thanks a lot for your work and the code you provided.

I was able to implenent it and realized an issue what I didnt made clear in the description.

In that table are more than 1 "Werks" (plants) with the same "MATNR" (products) and each plant should be only compared to itself products.

So I added an Index column to the query and inside each Index I added an inside Index.1 again.

From that I followed your M-Code.

And now I think the measure needs a little tweak to compute only in the same Index value.

The following attemtpt seems to solve this:

M = 
IF (
    MAX ( LieferscheineUnique[Modulo] ) = 0,
    CALCULATE (
        SUM(  LieferscheineUnique[fci_zu_PA] ),
        FILTER ( ALL ( LieferscheineUnique ), 
LieferscheineUnique[Integer-Division] = MAX ( LieferscheineUnique[Integer-Division]) ),
 LieferscheineUnique[Index] = MAX ( LieferscheineUnique[Index])   
    ) / 3,
    BLANK ()
)

FANTASTIC Henry! Thanks a lot!

Kind Regards

Marek

[Pillic]

@v-henryk-mstf 

Unfortunately my extension to your measure doesnt work properly. It calculates the wrong values now.

Do you know how to extend the measure the right way?