cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mholsen Regular Visitor
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:

Overbooked hours

 

You can find the PBIX file here:

Overbooked hours

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum measure total on only negative values

 

@Mholsen 

 

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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
paranoodle Regular Visitor
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")

Capture.PNG

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

Re: Sum measure total on only negative values

 

@Mholsen 

 

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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Mholsen Regular Visitor
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?