Sum measure total on only negative values

[Anonymous]

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 employee name and year-month, but the total column is incorrect still.

This is the measure, which is built with inspiration from these two posts by @Greg_Deckler :

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

Only overbooked hours = 
VAR sumtable = SUMMARIZE(Employees;Employees[Emp ID];"Overbooked hours";[Cap-Allo];"Sign";SIGN([Cap-Allo]))
VAR onlyoverbookedhours = CALCULATE(SUMX(FILTER(sumtable;[Sign]<0);[Overbooked hours])*-1;ALL('Date'[Year & Month]))
RETURN
IF(HASONEVALUE('Date'[Year & Month]);onlyoverbookedhours;SUMX(FILTER(sumtable;[Sign]<0);[Overbooked hours]))*-1

As you can see it works in the matrix, but the total is not summing only the negative values:

You can find the PBIX file here:

Overbooked hours

[Vvelarde · Accepted solution]

@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]

It appears to be the correct solution!

Can you elaborate on how your measure works?

[Anonymous]

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!