Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Incremental Average Where Average Increments by Fixed Range Quantity

Hi all,   I need to create a calculated column that will take the geometric average (geomean) of all values in the Qe column that are between two depth values (0.4ft below and 0.2ft above the curre...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Not sure if I got the + and - part right, but this approach should work for your column.

     

    Geomean =
    VAR currentdepth = Depth[Depth (ft)]
    RETURN
    CALCULATE (
    GEOMEAN ( Depth[Qe] ),
    ALL ( Depth ),
    VALUES ( Depth[ParentID] ),
    Depth[Depth (ft)] >= currentdepth - 0.2,
    Depth[Depth (ft)] <= currentdepth + 0.4
    )
     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    6 years ago

    One of my colleagues helped figure this one out, not sure if this is a best practice in DAX or not but it worked:

     

    Geomean =
    VAR currentdepth = Depth[Depth (ft)]

    VAR currentParentID = ( Depth[ParentID] )
    RETURN
    CALCULATE (
    GEOMEAN ( Depth[Qe] ),
    ALL ( Depth ),
    currentParentID = ( Depth[ParentID] ),
    Depth[Depth (ft)] >= currentdepth - 0.2,
    Depth[Depth (ft)] <= currentdepth + 0.4
    )

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Not sure if I got the + and - part right, but this approach should work for your column.

 

Geomean =
VAR currentdepth = Depth[Depth (ft)]
RETURN
CALCULATE (
GEOMEAN ( Depth[Qe] ),
ALL ( Depth ),
VALUES ( Depth[ParentID] ),
Depth[Depth (ft)] >= currentdepth - 0.2,
Depth[Depth (ft)] <= currentdepth + 0.4
)
 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for the quick reply!

 

This works, except it is taking the geomean within the correct depth range, but it is performing the calculation across multiple unique Parent ID's. If I remove the Values(Depth[ParentID]) line then I get the same result. How would I get this calculation to only be performed within the Qe and Depth (ft) values within each unique ParentID? For example, I have 400 to 500 Qe and corresponding Depth (ft) values for each unique ParentID, and I do not want geomean of Qe taken between different ParentID's.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    One of my colleagues helped figure this one out, not sure if this is a best practice in DAX or not but it worked:

     

    Geomean =
    VAR currentdepth = Depth[Depth (ft)]

    VAR currentParentID = ( Depth[ParentID] )
    RETURN
    CALCULATE (
    GEOMEAN ( Depth[Qe] ),
    ALL ( Depth ),
    currentParentID = ( Depth[ParentID] ),
    Depth[Depth (ft)] >= currentdepth - 0.2,
    Depth[Depth (ft)] <= currentdepth + 0.4
    )

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      I didn't see your response until now.  Glad that approach got you started in right direction.

       

      If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat