Change Format for Measure

[tejasp]

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

The result which I am expecting is in the "[HH]:MM" formate but the output looks like below:

Can anyone please advise what might be wrong here?

[Anonymous]

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.

[tejasp]

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

[Anonymous · Accepted solution]

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.

[HarishKM]

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

[tejasp]

@HarishKMThank you for your response. By using this dax it tend to show higher number than actual. I think the mod function is causing a trouble, when agent is scheuled HH:30 hours.

[edhans]

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.

[tejasp]

Hi @edhans thanks for the response, I had already tried that but its not helping. Along with it I also tried to use Formate{SUM(Schedules[Working Hours for Activities])+SUM(Schedules[ADMIN]),"hh:nn") but its not helpful either.

below is the output in PowerBI:

using same filter output in Excel:

[edhans]

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]

Hi @edhans apologies if the request was not clear.

Below is how my data looks like:

From This data I would like to get achieve things like shrinkage %, total working hours, Scheduled hours etc.