Forum Discussion
Change Format for Measure
Hi Team,
I have a table that contains data for logging hours on various statuses.
I have created a measure that sums a few of the statuses
(Scheduled_Hours = SUM(Schedules[Working Hours for Activities])+SUM(Schedules[ADMIN]).
Can anyone please advise what might be wrong here?
Hi tejasp ,
Thanks for your reply. The default time format in Power BI is 24 hour system. The format you requested is only text format in Power BI, so it cannot be used for calculations. I suggest that you could convert the time format into a decimal format to facilitate calculations. Just like this as follows.
Scheduled hours_new =
SUMX (
FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),
HOUR ( [D1] ) + HOUR ( [D2] )+(MINUTE ( [D1] ) + MINUTE ( [D2] ))/60
)Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Click on the measure and the Measures Tools tab should become active. Pick the time format. this one is all the way at the bottom.
You are going to have to provide some data tejasp - You wanted HH:MM, and that is what I gave you, and now you are actually showing HH:MM and saying it isn't right, then you show 08:00 for the first record, but Excel shows 504:00.
I have no idea what your data is and what is expected based on that.How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
tejasp Hey mate ,
Kindly refer below image and use that dax formula to fulfill your requirement .total min =SUMX ( 'Sheet1', HOUR ( Sheet1[Shift 1 ] ) )+ TRUNC ( SUMX ( 'Sheet1', MINUTE ( Sheet1[Shift 1 ] ) ) / 60 ) & ":"& MOD ( SUMX ( Sheet1, MINUTE ( Sheet1[Shift 1 ] ) ), 60 ) & ":00"Kudos will be appriciated .
Hi tejasp ,
Based on your description, I think you may be want a time format that does not actually have to conform to the 24-hour clock.
I did a test. Here is my test table. you can create a measure as follows.
Scheduled hours =
SUMX (
FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),
HOUR ( [D1] ) + HOUR ( [D2] )
)
+ INT (
SUMX (
FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),
MINUTE ( [D1] ) + MINUTE ( [D2] )
) / 60
) & ":"
& FORMAT (
MOD (
SUMX (
FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),
MINUTE ( [D1] ) + MINUTE ( [D2] )
),
60
),
"#00"
)Result:(Result in Excel)(Result in Power BI Desktop)
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymousthank you for your inputs, the dax helped me to achive the number but its not useful if I am trying to do any other mathematical operation, ( Late hours/ Scheduled hours) etc.
Hi tejasp ,
Thanks for your reply. The default time format in Power BI is 24 hour system. The format you requested is only text format in Power BI, so it cannot be used for calculations. I suggest that you could convert the time format into a decimal format to facilitate calculations. Just like this as follows.
Scheduled hours_new =
SUMX (
FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),
HOUR ( [D1] ) + HOUR ( [D2] )+(MINUTE ( [D1] ) + MINUTE ( [D2] ))/60
)Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
