Forum Discussion
Solved
Data category duration not working
Hello,
I have a column containing working hours as minutes, eg. "48572", "5829" ...
I need to translate those into hours and minutes, and afterwards I'd like to display it in a visual over time to compare the average working hours in different months.
I used the folliwing code:
"Duration in hours =
VAR Hours = QUOTIENT(AVERAGE('Table'[duration in minutes]); 60)
VAR Minutes = MOD(AVERAGE('Table'[duration in minutes]);60)
Return
TIMEVALUE(FORMAT(TIME(Hours;Minutes;00);"hh:mm"))"
1st problem: it doesn't display hours >= 24.
2nd problem: I cannot display the working hours in a visual (except for quickinfo). I cannot change the data category, it's marked as "not categorized".
Thank you very much for your help!
That will not work because the bar chart needs a number or a count and you are providing a text value.
The option you have is to do the normal sum and then use the previous measure as a tooltip.
7 Replies
Hi danielakunz ,
You should not use the format of hours but make the concatenation of the values so make the syntax of your formula like this:
Duration in hours = VAR Hours = QUOTIENT ( AVERAGE ( 'Table'[duration in minutes] ); 60 ) VAR Minutes = MOD ( AVERAGE ( 'Table'[duration in minutes] ); 60 ) RETURN Hours & ":" & Minutes & ":00"Also be aware that making this calculation of the average you can get values that are not correct format for the minutes, maybe you should use the SUM? This depends on what you want to achieve.
Thank you for your quick answer! Unfortunately, I still can't use the duration in a visual to display it properly. Do you have a suggestion to fix that?
Thank you!
