cancel
Showing results for
Did you mean:
Frequent Visitor

## SUM LOOKUPVALUE - Attempting to sum units with multiple criteria

Hi Power BI forum,

I have a vast table of transaction data and I'm hoping to find the total utilised time per staff per month in a table that looks similar to the one below. I'm struggling to find the right formula to use in my calculated column. Appreciate any suggestions.

 StaffName UnitsLogged IsUtilised MonthRecorded CalculatedColumn Sally 5 True 202010 5 Sally 2 False 202010 5 Bob 6 True 202010 9 Bob 3 True 202010 9 Bob 7 False 202010 9 Sally 4 True 202009 11 Sally 7 True 202009 11 Sally 7 False 202009 11 Bob 5 False 202009 8 Bob 8 True 202009 8

Thanks!

1 ACCEPTED SOLUTION
Super User IV

@PowerMyBI , Try a new column like

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ="True"),[UnitsLogged])

if IsUtilised is a boolean

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ),[UnitsLogged])

Proud to be a Super User!

2 REPLIES 2
Super User IV

@PowerMyBI , Try a new column like

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ="True"),[UnitsLogged])

if IsUtilised is a boolean

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ),[UnitsLogged])

Proud to be a Super User!

Frequent Visitor

@amitchandak that works perfectly! Thank you for help

Announcements