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.
| ParentID | Depth (ft) | Qe | Qe (geomean) |
| 5.000 | 14.30 | 71.467 | NA |
| 5.000 | 14.37 | 88.993 | NA |
| 5.000 | 14.44 | 105.308 | NA |
| 5.000 | 14.50 | 118.375 | 104.629 |
| 5.000 | 14.57 | 122.578 | 108.511 |
| 5.000 | 14.63 | 124.480 | 111.827 |
| 5.000 | 14.70 | 124.457 | 112.951 |
| 5.000 | 14.76 | 122.672 | 112.636 |
| 5.000 | 14.83 | 116.447 | 111.392 |
| 5.000 | 14.89 | 109.397 | 110.079 |
| 5.000 | 14.96 | 104.377 | 108.725 |
| 5.000 | 15.03 | 99.520 | 107.309 |
| 5.000 | 15.09 | 99.340 | 105.761 |
| 5.000 | 15.16 | 102.127 | NA |
| 5.000 | 15.22 | 104.766 | NA |
| 5.000 | 15.29 | 107.587 | NA |
| 5.000 | 15.35 | 108.634 | NA |
| 5.000 | 15.42 | 107.743 | NA |
| 5.000 | 15.49 | 104.557 | NA |
| 5.000 | 15.55 | 101.423 | NA |
