Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello all,
Quite new to BI.
I have a table containing a column of the 'Training time' of several tasks, and another column containing its respective 'Admin time'. The format of both column is set as TIME (HH:MM:SS).
To put it in contect, I want to have three visual cards: Overall Time, Training Time, and Admin Time. I have managed to sort the Training Time and Admin time one, but can't seem to manage doing the sum of these two.
My question is how do I add the Training Time and Admin time columns in Power Query to get an overall time ?
Or
How to add the two measures I created respectively for the total sum of the training time and admin time.
For the sum of either Training time or the Admin time, my dax measure is below;
Solved! Go to Solution.
@AntBI26 , Consider changing the data types of the 'Training Time' and 'Admin Time' columns (or duplicates of those) to Decimal in Power Query. 'Total Time' can then be added as a new column by adding the first two.
You may also want to refer the solution given in this post:
Solved: Problems with hours greater than 24 hours - Microsoft Fabric Community
Cheers,
Amol
@AntBI26 , Consider changing the data types of the 'Training Time' and 'Admin Time' columns (or duplicates of those) to Decimal in Power Query. 'Total Time' can then be added as a new column by adding the first two.
You may also want to refer the solution given in this post:
Solved: Problems with hours greater than 24 hours - Microsoft Fabric Community
Cheers,
Amol
Many thanks for your help @amolrs, it works.
I have attached the process I did below just in case it is relevant to someone else.
Best wishes
Thanks @tharunkumarRTK for responding. I have tried it , but it seems I am getting an Errror (Check photo 1)
I wonder if it because my columns (Training time and Admin time) are set as 'Time' data types ?
I have changed the data types as to your example, but then it seems the calculation is wrong (hours) is concerned (see photo 2):
@AntBI26
Assuming your requirement is to add two durations (Admin Time and Training Time). In power query you can achieve it using the below formula. Paste this formula in the custom column window
Text.Combine(List.Transform(Table.AddColumn(Table.FromColumns(
{List.Transform(Text.Split([Training Time], ":"), each Number.From(_)),
List.Transform(Text.Split([Admin Time], ":"), each Number.From(_))}, {"Training Time", "Admin Time"}), "Addition", each [Training Time] + [Admin Time])[Addition], each Text.PadEnd (Text.From( _ ),2,"0")), ":")
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |