I need assistance with calculating the sum of all the time taken for tasks being done. None of the existing solutions I have tried work as I need them to. The RADACAD example works for the card visual.
I have a two tables that have the same columns but are for two different departments, I could append the tables as a single table, but I don't think it makes a difference at this point.
The source files are imported from xlsx and the column Duration is General and appears as 03:10:10 It is then imported with Power Query using the Folder option and making changes to the sample file.
I need to be able to sum this duration column in Power BI, however I do need to calculate total per user, filtering different dates.
I have used transorm to duration, then in DAX SUMX and SUM the values using the var method, splitting and concatinating these values to RETURN a value.
This is all good and well if I need to display the total on a card but does not work when using it in a table layout for users not generating data.
My Employee table has everyone in, I can filter by department, group, function, manager or any of the other filter values.
My data:
| Employee | Duration |
| Emp1 | 00:00:00 |
| Emp1 | null |
| Emp2 | 00:00:08 |
| Emp2 | null |
| Emp2 | 00:00:00 |
| Emp1 | 00:26:51 |
| Emp3 | 00:00:17 |
| Emp4 | 00:00:09 |
My requirement is as follows:
I need to display the value in hours exceeding 24 hours. As an example 145:33:24
When using the table visual, I drop the group and employeename in the rows field. I also use a slicer on group.
In the values field, I cannot use the DAX calculated duration as it then provides everyones time in the rows.
I could filter, lock and hide the department, but the problem I have using this is the employees with no data/duration shows the time as :: which is not a usable value and just looks wrong.
The values are not usable in a chart as they are in TEXT format.
I have spent a lot of time looking for and trying various solutions and none work with the charts or tables thus far.
Please help?
(Are there future plans for PBI format to include [h]:nn:ss so that it works beyond 24 hrs or Duration to be included in the table Data Type after applying transformation? I don't know who to direct this to as it is probably the devs.)
