Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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.
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.
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
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?
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |