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:
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:
NOTE:
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
This is the result and the picture on the right, it is just to illustrate easily my meaning:
NOTE:
var column1 = CALCULATE(VALUES('table'[argument1]),OFFSET(-1,,ORDERBY('table'[argument2],DESC))
,REMOVEFILTERS())
RETURN
column1 + CALCULATE(VALUES('table'[argument1]))
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]))
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:
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
var groupcat = CALCULATE(MIN('table'[argument1]),OFFSET(-1,ALL('table'),ORDERBY('table'[argument2]),,
PARTITIONBY('table'[argument3])))
RETURN
groupcat + CALCULATE(MIN('table'[argument1]))
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])))
And for the column/measure 5:
CALCULATE(SUM('table'[argument1]),WINDOW(-1,REL,0,REL,ALL('table'),ORDERBY('table'[argument2]),,
PARTITIONBY('table'[argument3])))
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])))
For the second one, I want the total:
CALCULATE(SUM('table'[argument1]),WINDOW(1,ABS,-1,ABS,ALL('table'),ORDERBY('table'[argument2])))
And for the last one, I want a cumulative:
CALCULATE(SUM('table'[argument1]),WINDOW(1,ABS,0,REL,ALL('table'),ORDERBY('table'[argument2])))
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.
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...