Total Login Time per Agent (More than 24 hours)

[ChromeMystic]

Hi, I am still pretty new to Power Bi and I m currently trying to change our old Excel Dashboard to Power BI. One of the Reports we use gives a value of hh:mm:ss for each day on how long every agent was logged into the system. After a lot of swearing (The Original Report comes from a system that adds all kinds of weird formating that I had to remove, and we have no way to export it directly in a usable format, so I wrote a macro to remove all the weirdness and export a clean file).
I converted the Entries to durations and now I am trying to build a visual that gives me the Total of hours the agent worked this month (for now I only use a 1 month report so that I don't need an aditional Date filter).
I spent the last 5 hours searching these and other forums and watching tons of tutorials and the best i managed is the following measure

LoginTime = FORMAT(SUM(Agent_Login[@RCW_LoggedInTime]);"hh:mm:ss")

It seems to calculate the sum correctly, but I cannot get it to give me a accurate total, it basically omits the days.
In Excel I solved this by using the "[hh]" format but this isn't available in Power BI

During my search I found several proposals that had formulas of more than 20 lines or so but neither was I able to change them in order to work for me and also it seems weird to me that I need to write this much code just to add up some time.

Any tips on where to look or how to do what I need to do are appreciated.

[ChromeMystic]

I managed to use the script in https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 to create a new column, now PowerBI tells me that it cannot convert text....

[v-cherch-msft]

Hi @ChromeMystic 

Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.

Regards,

[ChromeMystic]

After hours of trial and error I forgot basics of asking for help. Now I am back at the Office and here are some additional Infos.

The Query I use is named "Agent_Login" and it imports the data from our Phone System. After cleaning up the data it looks like this:

@RCW_AgentName	@RCW_LoggedInTime	@RCW_Timestamp
Name1	09:07:38	01.06.2019 00:00
Name2	08:27:48	01.06.2019 00:00
Name3	07:06:56	01.06.2019 00:00
Name4	07:10:10	01.06.2019 00:00
Name5	09:01:25	01.06.2019 00:00
Name6	07:59:32	02.06.2019 00:00
Name5	09:08:49	02.06.2019 00:00
Name6	08:49:08	02.06.2019 00:00
Name7	06:58:29	02.06.2019 00:00
Name8	07:16:46	02.06.2019 00:00
Name9	08:59:46	03.06.2019 00:00
Name10	08:05:17	03.06.2019 00:00

I saved the Report as .csv before Importing it into Power BI.

I would like to calculate things like how many hours did all our agents log on a given day or how many hours did a single agent log over the course of a month. As I described before I can't get it to work with durations > 24hours. 

The Column @RCW_LoggedInTime is Formatted as Duration and the @RCW_Timestamp as Date. I also renamed them Inside Power BI to make them conform with the reports I get from the other systems (Timestamp was renamed to "Date" for instance).

My expected Outcome would be that when I get a report over all logged hours on the 02.06.2019 the Total  would add up to 81:06:41. At the Moment I either get errors and no results at all or 09:06:41

Edit: I forgot to mention, that the Timestamps of the Duration are shown as decimals in the Table view unless I manually change them to "time": 

[ChromeMystic · Accepted solution]

I managed to solve my problem - i converted the Timestamp into seconds

LoggedInSeconds = DATEDIFF(DATE(1899;12;30);Agent_Login[@RCW_LoggedInTime];SECOND)

In order to get the views I need I used the Script from https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 with 

SUM(Agent_Login[LoggedInSeconds])

I could also combine the two into 

SUM(DATEDIFF(DATE(1899;12;30);Agent_Login[@RCW_LoggedInTime];SECOND))

but I chose not to, as I can use the LoggedInSeconds in order to do quick checks of my calculations