Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Geometric Mean for Grouped Data

Hello Team,

 

Could you please help me with this I want to get the GeoMean for below example. I have groped data and I want to creat a cutom column that calculate a the Geometric Mean for the Duration. I want to creat the column inside the power query not do it AS MEASURE or COLUMN on dax. below table showning the data that are grouped by and I calculate the Geomean on excel so How I can do it in the same way in the Power Query any idea ? or Formula it can help me please?

 

DirectionJob TypeConveyanceField NameBaseDurationGeo Mean
UPOHWirelineAAAOIL1.53.170662853
UPOHWirelineAAAOIL2.53.170662853
UPOHWirelineAAAOIL8.53.170662853
DOWNVSPDrillpipeBBBWater1.54.181723304
DOWNVSPDrillpipeBBBWater6.54.181723304
DOWNVSPDrillpipeBBBWater7.54.181723304
UPFTSWirelineCCCOIL2.53.708099244
UPFTSWirelineCCCOIL5.53.708099244

 

Thank you in Advance for helping

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi Anonymous ,
    I would recommend to do a grouping using the UI with a dummy aggregation and then replace it with the formula for the geometric mean: 

    Number.Power(List.Product([Duration]), 1 / List.Count([Duration]))

    Please paste the following code into the advanced editor and follow the steps:

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCg1Q0lHy9wAS4ZlFqTmZealApqOjI0jU0wdIGuqZKsXqEKHQiFiFFlCFLv7hfkBuWDBIg0tRZk5OQWYBSLGTkxNId2JJahGSA4hUbkaacnNkV7uFBKM629nZGbv/8Ko0BamMBQA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [
                Direction = _t,
                #"Job Type" = _t,
                Conveyance = _t,
                #"Field Name" = _t,
                Base = _t,
                Duration = _t
            ]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Direction", type text},
            {"Job Type", type text},
            {"Conveyance", type text},
            {"Field Name", type text},
            {"Base", type text},
            {"Duration", type number}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Direction", "Job Type", "Conveyance", "Field Name", "Base"},
        {
            {
                "Geo Mean",
                each Number.Power(List.Product([Duration]), 1 / List.Count([Duration])),
                type nullable number
            },
            {"Partition", each _}
        }
    ),
    #"Expanded Partition" = Table.ExpandTableColumn(
        #"Grouped Rows",
        "Partition",
        {"Duration"},
        {"Duration"}
    )
in
    #"Expanded Partition"​



1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi Anonymous ,
    I would recommend to do a grouping using the UI with a dummy aggregation and then replace it with the formula for the geometric mean: 

    Number.Power(List.Product([Duration]), 1 / List.Count([Duration]))

    Please paste the following code into the advanced editor and follow the steps:

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCg1Q0lHy9wAS4ZlFqTmZealApqOjI0jU0wdIGuqZKsXqEKHQiFiFFlCFLv7hfkBuWDBIg0tRZk5OQWYBSLGTkxNId2JJahGSA4hUbkaacnNkV7uFBKM629nZGbv/8Ko0BamMBQA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [
                Direction = _t,
                #"Job Type" = _t,
                Conveyance = _t,
                #"Field Name" = _t,
                Base = _t,
                Duration = _t
            ]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Direction", type text},
            {"Job Type", type text},
            {"Conveyance", type text},
            {"Field Name", type text},
            {"Base", type text},
            {"Duration", type number}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Direction", "Job Type", "Conveyance", "Field Name", "Base"},
        {
            {
                "Geo Mean",
                each Number.Power(List.Product([Duration]), 1 / List.Count([Duration])),
                type nullable number
            },
            {"Partition", each _}
        }
    ),
    #"Expanded Partition" = Table.ExpandTableColumn(
        #"Grouped Rows",
        "Partition",
        {"Duration"},
        {"Duration"}
    )
in
    #"Expanded Partition"​