Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I currently have a report set up that uses the max value for a day to give me someones logout time and login time. I then have a formula to subtract one from the other to give me the time that the person was logged in. (I know this is not a perfect measure of login time but for my purposes it works well.) The Formulas are :
Login = MIN('ReportA_AgentActivityLog'[Total Agents Needing Help])
Logout = MAX('ReportA_AgentActivityLog'[Total Agents Needing Help])
Lunch = Lunch = SUM('ReportA_AgentActivityLog'[LunchRaw])
I then have the data laid out in a matrix to show each departments list of employees. I am trying to figure out a way to total off of these.
Because these are measure I cant simply sum them. Does anyone have any ideas?
Solved! Go to Solution.
Hi,
Try this measure
Worked = SUMX(SUMMARIZE(VALUES(ReportA06192018[Date]),[Date],"ABCD",[Logout]-[Login]-[Lunch]),[ABCD])
Click on the Matrix and under the Format sectino of the visualisations pane, switch the column totals on under the Subtotals section. I cannot remove the Login, Logout and Lunch from the Total section. This can be done in Excel though.
Hope this helps.
Hi @Anonymous
You probably need to use SUMX and pass your day column as the first parameter with your [Lunch] measure as the second parameter.
Do you have a small set of same data?
@Phil_Seamark THe link below is to a scrubbed set of sample data.
https://www.dropbox.com/s/0rjws0fy4zost1j/ReportA06192018.csv?dl=0
Any help you can give me i would appricate! I have tried using the SumX function and cant seem to figure it out.
This is what the sheet looks like in my report. I am sure that I will ahve to make another Table or Matrix to house only the totals but i cant seem to get all of it to work together.
Hi @Anonymous
I downloaded your sample data and loaded it into Power BI, but just struggling to get something that looks like your screenshot so I can help with the SUM calc.
I've attached the file to this message, so perhaps download and tidy so we have a matrix, then send it back.
I realized that i had scrubbed a little to much from the file. I added the lunch column back in. PBIX file is below:
Hi @Anonymous
Please try this calculated measure. I have attached the PBIX file.
Lunch = IF( ISFILTERED('ReportA06192018'[Agent Name]), -- NORMAL LINE--- SUM(ReportA06192018[LunchRAW]), -- TOTAL LINE --- SUMX( VALUES('ReportA06192018'[Agent Name]), CALCULATE( SUM(ReportA06192018[LunchRAW]) ) ) )
I have been playing with this and I can sum each day into an hours worked column but I cant seen to find a way to Sum those hours worked column to give a total for the entire week. Any ideas?
Hi,
Share the link from where i can download your PBI file.
Hi,
I get the wrong answer with these measures. May be someone else can help you.
Hours worked = [Logout]-[Login]-[Lunch]
Worked = SUMX(SUMMARIZE(VALUES(ReportA06192018[Agent Name]),[Agent Name],"ABCD",[Hours worked]),[ABCD])
The correct answer should be 64:03.
@Ashish_Mathur What I am trying to do is total the rows, not the columns. I want to have a total of hours worked for each agent so in your screen shop it would be
I am trhing to get that sum for each line.
Hi,
Try this measure
Worked = SUMX(SUMMARIZE(VALUES(ReportA06192018[Date]),[Date],"ABCD",[Logout]-[Login]-[Lunch]),[ABCD])
Click on the Matrix and under the Format sectino of the visualisations pane, switch the column totals on under the Subtotals section. I cannot remove the Login, Logout and Lunch from the Total section. This can be done in Excel though.
Hope this helps.
You are welcome.
Any suggestions for changing the output format?
Currently when the total gets to 24:00 it goes back to 0:01. I w ant it to keep counting so that it could in theory go to 104:00.
I looked at some of the online guides and they all deal with 12/24 hour change overs but i dont see any the corrospond to (in excel terms) HH:mm vs. [HH]:mm
Hi,
I do not know how to solve that in PowerBI desktop. Someone else will help you. Please stick to this thread so that i can learn from someone else's answer.
Thanks, Will do. We are all here to learn! 🙂 Its great having a community willing to teach each other!
@MarcelBeug I saw your suggestions on time values over 24 hours in another thead... I tried to apply your solution to this problem... but could not get it to work. Any suggestions?
I am trying to figure out a way to total the hours worked for the time period selected.... so basically
Mondays hours worked + tuesdays hours worked + wednesdays hours worked + Thrusdays hours worked + Fridays hours worked.
All of these displayed in a colum at the end...
the hours worked is just:
logout - login - lunch for each day.
This way You can see the hours worked for a given week on its own line.