itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: cumulative total values with empty cells

In a table with blank cells, there are different ways to show a cumulative result:

  • Cumulate values without taking care about those empty cells (for instance, column and column 2)
  • Cumulate values by restarting it from 0 after empty cells (for instance, column 4 and column 5)
power bi

Let’s me explain the different columns. For “column”, I will use the formula on this article Power BI: cumulative total values, but the result is not very clean.

power bi

For “column 2”, I am using the formula of “column” but the result is cleaner because I added this:

IF(NOT(ISBLANK('table'[argument1])),formula of column)

power bi

NOTE: replace table and argument1 by yours

For “column 4”, to reset the cumulative after each empty cells, I will need first to create the “column 3” to give an ID for each “result” group with this formula:

var gresult=IF(NOT(ISBLANK('table'[argument1])),CALCULATE(COUNTBLANK('table'[argument1]),FILTER('table',
'table'[argument2]<=EARLIER('table'[argument2]))))
return
IF(ISBLANK(gresult),IF(NOT(ISBLANK('table'[argument1])),0),gresult)

power bi

Once done, the formula of “column 4”:

IF(NOT(ISBLANK('table'[argument3])),CALCULATE(SUM('table'[argument1]),FILTER(ALL('table'),'table'[argument2]
<=EARLIER('table'[argument2]) && 'table'[argument3]=EARLIER('table'[argument3]))))

power bi

Now if I want to add a condition, for instance, to reset it for each “support” group, I will add:

&& 'table'[argument4]=EARLIER('table'[argument4])

This is what I did for “column 5”:

power bi
power bi

Interesting Topics