itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: calculate values by group

I have this table:

power bi

And I want to sum values for each group, in my example, the parent tickets. To do it, I will use this formula:

IF(NOT(ISBLANK('table'[argument1])),SUMX(FILTER('table',EARLIER('table'[argument1])='table'[argument1]),'table'[argument2]))

power bi

Now if I want to have the results to be in the same row as the matching value of the “number” column, I have 2 options, one with a new table and one without.

Option 1 without a new table

With a new column, I will use this formula:

SUMX(FILTER('table',EARLIER('table'[argument3])='table'[argument1]),'table'[argument2])

power bi

Option 2 with a new table

I will create a new table called “table2” by extracting the “number” and “parent” columns without the empty cells like that:

power bi

NOTE: to know how, read Power BI: extract columns from a table to another one.

In the “table1”, I will create a new column with this formula:

LOOKUPVALUE('table2'[argument1],'table2'[argument1],'table1'[argument3])

power bi

And this last one:

IF(NOT(ISBLANK('table1'[argument4])),SUMX(FILTER('table1',EARLIER('table1'[argument4])='table1'[argument1]),'table1'[argument2]))

power bi

NOTE:

power bi

Interesting Topics