Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |