Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum measure total on only negative values

Hi,   Generally, the measure must show when an employee is overbooked over a period. The measure needs only to sum the negative values of another measure. It works in a matrix filtered by employe...
Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

     

    Anonymous 

     

    Hi, letw try with this measure:

     

    Only overbooked hours =
SUMX (
    CROSSJOIN ( VALUES ( Employees[Name] ), VALUES ( 'Date'[Year & Month] ) ),
    IF ( [Cap-Allo] < 0, [Cap-Allo], BLANK () )
)

    Regards

     

    Victor

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I was able to get the subtotals working, but I don't have enough experience with this to figure out what PowerBI expects from the grand total, unfortunately.

 

For the subtotals you can do this:

cap2 = IF([Cap-Allo] < 0, [Cap-Allo], BLANK())
overbooked hours = 
var t_name = SUMMARIZE(Employees, Employees[Emp ID], "value", [cap2])
var t_date = SUMMARIZE('Date', 'Date'[Year & Month], "value", [cap2])

return SWITCH(TRUE(),
    HASONEVALUE(Employees[Emp ID]) && HASONEVALUE('Date'[Year & Month]), [cap2],
    HASONEVALUE(Employees[Emp ID]), SUMX(t_date, [value]),
    HASONEVALUE('Date'[Year & Month]), SUMX(t_name, [value]),
    "total")

I figure the grand total follows similar logic, but I'm not sure exactly what calculation leads there. I hope this is at least a starting point though!