cancel
Showing results for
Did you mean:
Mholsen Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Sum measure total on only negative values

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

Lima - Peru

Proud to be a Datanaut!

3 REPLIES 3
paranoodle Regular Visitor

Re: Sum measure total on only negative values

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! Super User

Re: Sum measure total on only negative values

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

Lima - Peru

Proud to be a Datanaut!

Mholsen Regular Visitor

Re: Sum measure total on only negative values

It appears to be the correct solution!

Can you elaborate on how your measure works?