Power BI: calculate values of a single column by row

To calculate the values of each row of a single column, I will use the OFFSET and/or WINDOW functions. There are different ways to do it but with those functions, I will use 1 or at least very few formula lines, moreover, with them, I can group the results by category in a simple way. Let´s say that I have this simple data:

power bi

I will start first with the OFFSET function and before to explain the calculation, I will explain the basic. OFFSET will display the result one row above or below with this formula:

  • One row below: CALCULATE(VALUES('table'[argument1]),OFFSET(-1,,ORDERBY('table'[argument2])),REMOVEFILTERS())
    power bi
  • One row above: CALCULATE(VALUES('table'[argument1]),OFFSET(1,,ORDERBY('table'[argument2])),REMOVEFILTERS())
    power bi

NOTE:

  • Replace “table” and “argument” by yours
  • By default, ORDERBY is ascending (ASC), for descending: ORDERBY('table'[argument2],DESC)
  • For a second condition of ordering: ORDERBY('table'[argument2],'table'[argument3]) or ORDERBY('table'[argument2],DESC,'table'[argument3],DESC)
  • If the OFFSET is using another table, I have to add “ALLSELECTED” or “ALL” like this:
    CALCULATE(VALUES('table1'[argument]),OFFSET(-1,ALL('table2'[argument]),ORDERBY('table2'[argument]))
    ,REMOVEFILTERS())
    power bi
  • The only difference is the “-1” and “1” and this is the result:
power bi

I will use the formula of the column 1 to sum each cell like this:

var column1 = CALCULATE(VALUES('table'[argument1]),OFFSET(-1,,ORDERBY('table'[argument2]))
,REMOVEFILTERS())
RETURN
column1 + CALCULATE(VALUES('table'[argument1])) // to subtract use this (column 4): CALCULATE(VALUES('table'[argument1])) - column1
power bi

This is the result and the picture on the right, it is just to illustrate easily my meaning:

power bi power bi

NOTE:

  • For the column 3 and column 5 (below), I can get the same result using the WINDOW function (only measure not column)
  • If I prefer to show the total of the result at the first row, I will use this formula:
var column1 = CALCULATE(VALUES('table'[argument1]),OFFSET(-1,,ORDERBY('table'[argument2],DESC))
,REMOVEFILTERS())
RETURN
column1 + CALCULATE(VALUES('table'[argument1]))
power bi
power bi

If I want to do the same calculation but only for a category group, I will use the PARTITIONBY sub-function of OFFSET. For instance, I want the result calculated and grouped based on the priority:

var groupcat = CALCULATE(VALUES('table'[argument1]),OFFSET(-1,,ORDERBY('table'[argument2]),,
PARTITIONBY('table'[argument3])),REMOVEFILTERS())
RETURN
groupcat + CALCULATE(VALUES('table'[argument1]))
power bi
power bi

NOTE for a second condition of grouping: PARTITIONBY('table'[argument3],'table'[argument4])

Alternatively, instead to use calculated columns, I can use only measures. The formula will be a little different but quite similar:

  • Measure 1 (column 1): CALCULATE(MIN('table'[argument1]),OFFSET(-1,ALL('table'),ORDERBY('table'[argument2])))
    power bi
    NOTE: If the OFFSET is using another table: CALCULATE(MIN('table1'[argument]),OFFSET(-1,ALL('table2'),ORDERBY('table2'[argument])))
    power bi
    NOTE: based on your data, “ALLSELECTED” will work better than “ALL”
  • Measure 2 (column 2): CALCULATE(MIN('table'[argument1]),OFFSET(1,ALL('table'),ORDERBY('table'[argument2])))
  • Measure 3 (column 3):
var measure1 = CALCULATE(MIN('table'[argument1]),OFFSET(-1,ALL('table'),ORDERBY('table'[argument2])))
RETURN
measure1 + CALCULATE(MIN('table'[argument1])) // to subtract use this (measure 4): CALCULATE(MIN('table'[argument1])) - measure1
power bi
  • Measure 5 (column 5):

var groupcat = CALCULATE(MIN('table'[argument1]),OFFSET(-1,ALL('table'),ORDERBY('table'[argument2]),,
PARTITIONBY('table'[argument3])))
RETURN
groupcat + CALCULATE(MIN('table'[argument1]))
power bi
power bi

Now, I will explain the WINDOW function that can be used only in measure (not in calculated column) for addition only (no subtraction). If you remember, I said that this function can be used to get the same results as the OFFSET for the column/measure 3 and column/measure 5. To have the same thing as the column/measure 3, I will use this formula:

CALCULATE(SUM('table'[argument1]),WINDOW(-1,REL,0,REL,ALL('table'),ORDERBY('table'[argument2])))

power bi

And for the column/measure 5:

CALCULATE(SUM('table'[argument1]),WINDOW(-1,REL,0,REL,ALL('table'),ORDERBY('table'[argument2]),,
PARTITIONBY('table'[argument3])))

power bi
power bi

As we can see, with WINDOW, I don’t need to use many calculations as OFFSET to get the same results. Beside that, WINDOW can do more things, I will explain 3 interesting scenarios. For the first one, imagine that I want to show only the sum of the “inc001 + inc002” so I can compare this result with others:

CALCULATE(SUM('table'[argument1]),WINDOW(1,ABS,2,ABS,ALL('table'),ORDERBY('table'[argument2])))

power bi
power bi

For the second one, I want the total:

CALCULATE(SUM('table'[argument1]),WINDOW(1,ABS,-1,ABS,ALL('table'),ORDERBY('table'[argument2])))

power bi
power bi

And for the last one, I want a cumulative:

CALCULATE(SUM('table'[argument1]),WINDOW(1,ABS,0,REL,ALL('table'),ORDERBY('table'[argument2])))

power bi
power bi

NOTE: some functions like ALL, USERELATIONSHIP, etc. work better after the WINDOW function

Alternatively, this article Power BI: calculate values of a single column by cell describes another way to do it by using an index/rank column and if you are interested about the cumulative calculation, read Power BI: cumulative total values.

Interesting Management