Sum of Max's...

[Anonymous]

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? 

[Phil_Seamark]

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?

[Anonymous]

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

[Phil_Seamark]

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.

[Anonymous]

I realized that i had scrubbed a little to much from the file. I added the lunch column back in. PBIX file is below: 

https://www.dropbox.com/s/i86l1gjzmvtz0wm/agent.pbix?dl=0

[Phil_Seamark]

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

[Anonymous]

@Phil_Seamark 

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? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur The link is here

https://www.dropbox.com/s/i86l1gjzmvtz0wm/agent.pbix?dl=0 

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur · Accepted 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.

[Anonymous]

@Ashish_MathurThank you! This worked great!

[Ashish_Mathur]

You are welcome.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

Thanks, Will do. We are all here to learn! 🙂 Its great having a community willing to teach each other! 

[Anonymous]

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

[Anonymous]

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.