Calculate average of values between defined values

[gregwinter]

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 🙂

[v-juanli-msft · Accepted 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.

[HotChilli]

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.

[gregwinter]

@HotChilli 

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.