Forum Discussion
How to aggregate values for the current row, previous row and next row in a new calculated column
Hi,
It's my first ever question in any such forum...please let me know if I haven't done it the right way.
I am trying to calculate the average based on the values in current, previous and next row.
Refer example below.
"Avg Value" is the calculated column I am trying to create.
Example - the Avg Value for row with Location 183.55 is 6 because it is the average of (2,8,3,3,4 & 16)
The query I tried is below, where "Merge2" is the table name-
| Line | Location | Left Value | Right Value | Avg Value |
| 828640 | 183.52 | 7 | 13 | 10 |
| 828640 | 183.53 | 8 | 12 | 8.33 |
| 828640 | 183.54 | 2 | 8 | 6.67 |
| 828640 | 183.55 | 3 | 3 | 6 |
| 828640 | 183.56 | 4 | 16 | 6.67 |
| 828640 | 183.57 | 5 | 15 | 8.33 |
| 828640 | 183.58 | 6 | 4 | 6 |
| 828640 | 183.59 | 7 | 9 | 7.33 |
| 828640 | 183.6 | 8 | 10 | 9 |
| 828640 | 183.61 | 9 | 11 | 7 |
| 828640 | 183.62 | 1 | 3 | 8.33 |
| 828640 | 183.63 | 12 | 14 | 10.33 |
| 828640 | 183.64 | 15 | 17 | 14.5 |
Thanks
Hi, Anonymous
You can use the following method to meet your needs:
Step1: Create a table in power bi.
Step 2: I create a measure as:
Avg =
var preleft =
CALCULATE(
MAX('Merge2'[Left Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] < MAX('Merge2'[Location])
)
)
)
)
var preright =
CALCULATE(
MAX('Merge2'[Right Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] < MAX('Merge2'[Location])
)
)
)
)
var nextleft =
CALCULATE(
MAX('Merge2'[Left Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] > MIN('Merge2'[Location])
)
)
)
)
var nextright =
CALCULATE(
MAX('Merge2'[Right Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] > MIN('Merge2'[Location])
)
)
)
)
return
IF(
HASONEVALUE('Merge2'[Location]),
IF(
MIN('Merge2'[Location]) =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line])
)
) ||
MAX('Merge2'[Location]) =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line])
)
),
(SUM(Merge2[Left Value]) + SUM('Merge2'[Right Value]) )/ 2,
((nextleft + nextright) / 2 + (preleft + preright) / 2 + (SUM(Merge2[Left Value]) + SUM('Merge2'[Right Value]) )/ 2 ) / 3
)
)
Step 3: Drag the measure into the table.
Here is the demo, please try it: How to aggregate values for the current row, previous row and next row in a new calculated column
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous , Try measure like
Last Location = CALCULATE(max('Table'[Location]),filter(ALLSELECTED('Table'),'Table'[Location] =MAXX(FILTER(ALLSELECTED('Table'),'Table'[Location]<max('Table'[Location])),'Table'[Location])))
next Location = CALCULATE(max('Table'[Location]),filter(ALLSELECTED('Table'),'Table'[Location] =MAXX(FILTER(ALLSELECTED('Table'),'Table'[Location]>max('Table'[Location])),'Table'[Location])))
or
Last Location = CALCULATE(max('Table'[Location]),filter(ALLSELECTED('Table'),'Table'[Location] =MAXX(FILTER(ALLSELECTED('Table'),'Table'[Location]<max('Table'[Location]) && 'Table'[Line]= max('Table'[Line]) ),'Table'[Location])))
next Location = CALCULATE(max('Table'[Location]),filter(ALLSELECTED('Table'),'Table'[Location] =MAXX(FILTER(ALLSELECTED('Table'),'Table'[Location]>max('Table'[Location]) && 'Table'[Line]= max('Table'[Line]) ),'Table'[Location])))
- v-xulin-mstf
Community Support
Hi, Anonymous
You can use the following method to meet your needs:
Step1: Create a table in power bi.
Step 2: I create a measure as:
Avg =
var preleft =
CALCULATE(
MAX('Merge2'[Left Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] < MAX('Merge2'[Location])
)
)
)
)
var preright =
CALCULATE(
MAX('Merge2'[Right Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] < MAX('Merge2'[Location])
)
)
)
)
var nextleft =
CALCULATE(
MAX('Merge2'[Left Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] > MIN('Merge2'[Location])
)
)
)
)
var nextright =
CALCULATE(
MAX('Merge2'[Right Value]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line]) &&
'Merge2'[Location] =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] = EARLIER(Merge2[Line]) &&
'Merge2'[Location] > MIN('Merge2'[Location])
)
)
)
)
return
IF(
HASONEVALUE('Merge2'[Location]),
IF(
MIN('Merge2'[Location]) =
CALCULATE(
MIN('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line])
)
) ||
MAX('Merge2'[Location]) =
CALCULATE(
MAX('Merge2'[Location]),
FILTER(
ALL(Merge2),
'Merge2'[Line] in DISTINCT('Merge2'[Line])
)
),
(SUM(Merge2[Left Value]) + SUM('Merge2'[Right Value]) )/ 2,
((nextleft + nextright) / 2 + (preleft + preright) / 2 + (SUM(Merge2[Left Value]) + SUM('Merge2'[Right Value]) )/ 2 ) / 3
)
)
Step 3: Drag the measure into the table.
Here is the demo, please try it: How to aggregate values for the current row, previous row and next row in a new calculated column
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
