Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
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
It appears to be the correct solution!
Can you elaborate on how your measure works?
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!