cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 current record) in another column for each unique ParentID (5 given as an example below for ParentID but there would be overlapping depth and Qe values for other unique ParentID's so these would need to be grouped by ParentID). See data structure below and expected result shown as Qe (geomean).  I can do this in Excel because the formula will increment by 1 for each row with this formula: 

 

=GEOMEAN(INDEX($A$53:$G$1508, MATCH(A53-0.2,$A$53:$A$1508, 1), COLUMN($G:$G)):INDEX($A$53:$G$1508, MATCH(A53+0.4,$A$53:$A$1508, 1), COLUMN($G:$G)))

 

Maybe I should refer to this as a moving geometric mean of a dynamic range of Qe values that is defined by a depth increment n - 0.2 and a n + 0.4.  I'm still a novice at Power BI, but it seems like this is something that could be done with defining a variable and an index, but I have had no luck as I'm struggling to tie together the concept of a dynamic range in Excel with the data structure in Power BI.

 

ParentIDDepth (ft)QeQe (geomean)
5.00014.3071.467NA
5.00014.3788.993NA
5.00014.44105.308NA
5.00014.50118.375104.629
5.00014.57122.578108.511
5.00014.63124.480111.827
5.00014.70124.457112.951
5.00014.76122.672112.636
5.00014.83116.447111.392
5.00014.89109.397110.079
5.00014.96104.377108.725
5.00015.0399.520107.309
5.00015.0999.340105.761
5.00015.16102.127NA
5.00015.22104.766NA
5.00015.29107.587NA
5.00015.35108.634NA
5.00015.42107.743NA
5.00015.49104.557NA
5.00015.55101.423NA
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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
)

View solution in original post

4 REPLIES 4
Highlighted
Super User VI
Super User VI

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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.

Highlighted
Frequent Visitor

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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
)

View solution in original post

Highlighted
Super User VI
Super User VI

Re: Incremental Average Where Average Increments by Fixed Range Quantity

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors