Sum Time By Category

[Anonymous]

Hi All,

I have a table with categories ( Red Alert, Yellow Alert etc) and times in munites and hours for each (5:10,10:00 etc). When I place the time on the values column to create a chart, it says "count of alert time". Is it possible to have sum of alert time?? Do I need to create a column formula or measure formula??

Also, when creating a dashabord and you want to have filters for staff to use, Is it possible to have it elsewhere that wont interfere with the look of the dashaboard???

Any ideas would be greatly appreciated.

[jthomson · Accepted solution]

It'd probably be easiest to try to convert your hours:minutes column into minutes (i.e. split it by delimiter into hours and minutes, make new column that multiplies hours by 60 and adds it to minutes) - that'd just be an integer that you can sum on to show your total, you can use formatting on that to get things to display as hours and minutes if needed. Pretty common requirement given PBI doesn't handle durations well, have a search

Easiest to dump filters into a filter pane so that they can be collapsed down out of the way for the most part

[jthomson · Accepted solution]

It'd probably be easiest to try to convert your hours:minutes column into minutes (i.e. split it by delimiter into hours and minutes, make new column that multiplies hours by 60 and adds it to minutes) - that'd just be an integer that you can sum on to show your total, you can use formatting on that to get things to display as hours and minutes if needed. Pretty common requirement given PBI doesn't handle durations well, have a search

Easiest to dump filters into a filter pane so that they can be collapsed down out of the way for the most part

[Anonymous]

Hi,

Can I convert all to minutes with a formula without splitting the column??

[Anonymous]

Hi,

Do I need to create a filter pane?? I am working desktop and want to publish to service. How does that work??

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

As for your first queston:

You can use LEFT() and RIGHT() functions to get minutes(hour*60 and minute) from your original data.(like hh:mm:ss). Then transform it to be a number with VALUE() function and add together. 

As for your second queston:

The filter will keep the status which is the same as your desktop. You can let it show default value with this operation.