Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate average of values between defined values

Hi all,

 

I am looking to find the average of some values that lie between changover being 'Yes'.

 

PartCycle Time (minutes)ChangeoverStatus
15.9No
15.2No
15.7No
117.5Yes
26.1No
26.7No
26.3No
26.3No
215.6Yes
33.6No
33.7No
33.2No

 

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 🙂

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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.

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    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.