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.
Hi all,
I am looking to find the average of some values that lie between changover being 'Yes'.
Part | Cycle Time (minutes) | ChangeoverStatus |
1 | 5.9 | No |
1 | 5.2 | No |
1 | 5.7 | No |
1 | 17.5 | Yes |
2 | 6.1 | No |
2 | 6.7 | No |
2 | 6.3 | No |
2 | 6.3 | No |
2 | 15.6 | Yes |
3 | 3.6 | No |
3 | 3.7 | No |
3 | 3.2 | No |
I am looking to get an average of each part excluding the changeover part i.e. for part 1 should be (5.9+5.2+5.7)/3.
However, there are multiple instances of the same part number through the document but each should be treated as unique - so effectively, a count of each range between 'Yes' and 'Yes'.
Any help is greatly appreciated!
Greg 🙂
Solved! Go to Solution.
Hi @gregwinter
Thanks to HotChilli's suggestion, modify it and create a measure as below, please check if it works for your case.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLVswSSfvlKsTowASN0AXNUAUNzPVMgFZlaDBYCKTfTM0SogQiYowsYExAwNNUzQzIWJGsMFoGqgQiYowsYoQqYgI2FGQJylZGeBZr70T1kAhYA64kFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, #"Cycle Time (minutes)" = _t, ChangeoverStatus = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", Int64.Type}, {"Cycle Time (minutes)", type number}, {"ChangeoverStatus", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YesinCol", each if [ChangeoverStatus] = "Yes" then 1 else 0),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.Range(#"Added Index"[YesinCol], 0, [Index]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Custom index"}})
in
#"Renamed Columns"
Measure =
CALCULATE (
AVERAGE ( Query1[Cycle Time (minutes)] ),
FILTER (
ALLSELECTED ( Query1 ),
Query1[Part]
= MAX ( Query1[Part] )
&& Query1[ChangeoverStatus] = "No"
&& Query1[Custom index]
= MAX ( Query1[Custom index] )
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gregwinter
Thanks to HotChilli's suggestion, modify it and create a measure as below, please check if it works for your case.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLVswSSfvlKsTowASN0AXNUAUNzPVMgFZlaDBYCKTfTM0SogQiYowsYExAwNNUzQzIWJGsMFoGqgQiYowsYoQqYgI2FGQJylZGeBZr70T1kAhYA64kFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, #"Cycle Time (minutes)" = _t, ChangeoverStatus = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", Int64.Type}, {"Cycle Time (minutes)", type number}, {"ChangeoverStatus", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YesinCol", each if [ChangeoverStatus] = "Yes" then 1 else 0),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.Range(#"Added Index"[YesinCol], 0, [Index]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Custom index"}})
in
#"Renamed Columns"
Measure =
CALCULATE (
AVERAGE ( Query1[Cycle Time (minutes)] ),
FILTER (
ALLSELECTED ( Query1 ),
Query1[Part]
= MAX ( Query1[Part] )
&& Query1[ChangeoverStatus] = "No"
&& Query1[Custom index]
= MAX ( Query1[Custom index] )
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here's some PQ code (the strategy is to add a column which identifies each partition)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLVswSSfvlKsTowASN0AXNUAUNzPVMgFZlaDBYCKTfTM0SogQiYowsYExAwNNUzQzIWJGsMFoGqgQiYowsYoQqYgI2FGQJylZGeBZr70T1kAhYA64kFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, #"Cycle Time (minutes)" = _t, ChangeoverStatus = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", Int64.Type}, {"Cycle Time (minutes)", type number}, {"ChangeoverStatus", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YesinCol", each if [ChangeoverStatus] = "Yes" then 1 else 0),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.Range(#"Added Index"[YesinCol], 0, [Index]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"YesinCol", "Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([ChangeoverStatus] = "No"))
in
#"Filtered Rows"
Close and Apply.
In PBI front-end, create a measure for the average of the cycle time column.
Pull the Part, the Custom and the measure on to a table.
Thanks for the time you spent on this issue!
If I undertsand the PQ code correctly, I am at the same stage using DAX queries.
I have created filters to ignore the Changeover Time - the main issue is being able to make small groups of calculations down a column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.