Forum Discussion
Same calculations on multiple columns
I am new to Power BI desktop and I am trying to conceptually understand the best way to tackle this problem. So I have a table that looks like this: Index FIFO2_1 FIFO2_2 FIFO2_3 FI...
Ashish_MathurThanks Ashish, I was actually able to solve the problem by pivoting the table in the following way. And then working with rows instead of columns.
Index FIFO2 Value
0 FIFO2_1 0.097 0 FIFO2_2 0.096 0 FIFO2_3 0.101 0 FIFO2_4 0.096 ... ... ... 30 FIFO2_6 0.126 30 FIFO2_7 0.064
Changing the DAX to:AverageBetween400and440 = CALCULATE( AVERAGE('Table'[Value]), 'Table'[Index] >= 15 && 'Table'[Index] <= 20 ) transformed_FIFI2 = [AverageBetween400and440] - SUM('Table'[Value]) Avg Last 10 Max = var maxV = MAX('Table'[Value]) var maxI = CALCULATE(MAX('Table'[Index]), 'Table'[Value] = maxV) RETURN CALCULATE( AVERAGE('Table'[Value]), 'Table'[Index] > maxI - 10 && 'Table'[Index] <= maxI )
Ashish_MathurThanks Ashish, I was actually able to solve the problem by pivoting the table in the following way. And then working with rows instead of columns.
Index FIFO2 Value
| 0 | FIFO2_1 | 0.097 |
| 0 | FIFO2_2 | 0.096 |
| 0 | FIFO2_3 | 0.101 |
| 0 | FIFO2_4 | 0.096 |
| ... | ... | ... |
| 30 | FIFO2_6 | 0.126 |
| 30 | FIFO2_7 | 0.064 |
Changing the DAX to:
AverageBetween400and440 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] >= 15 && 'Table'[Index] <= 20
)
transformed_FIFI2 = [AverageBetween400and440] - SUM('Table'[Value])
Avg Last 10 Max =
var maxV = MAX('Table'[Value])
var maxI = CALCULATE(MAX('Table'[Index]), 'Table'[Value] = maxV)
RETURN
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] > maxI - 10 && 'Table'[Index] <= maxI
)