Forum Discussion
Aggregating Custom formatted numbers
First, I was able to take your Time Per Patient and create the duration field more easily. Simply add a custom column that takes [Time Per Patient]/86400. (86400 is seconds per day). Then, convert the data type to "Duration"
You can have charts that display duration info in aggregated form, such as a line or bar chart (provided you have an appropriate X axis for your line chart, such that each row of your patient time has a month or day of week or whatever). However, the display of the value will be in decimal form, and thus is not very useful for you if you wish to see values in hh:mm:ss format.
See posts on this board: http://community.powerbi.com/t5/Desktop/Duration-as-Y-Axis/td-p/82651
If you wish to create measures, you should be able to aggregate first then format the measure. In other words, you would use DAX to create your measure (i.e. difference in average time this period vs last period) on the underlying data [Time Per Patient], and nest this function inside of a format function to have it return the result as you prefer.
Example:
Avg.Time = FORMAT(CALCULATE(AVERAGE(PatientTimes[FormattedTime]),ALL(PatientTimes)),"hh:mm:ss")
The red code is your filter argument. I don't have any fields to filter as I literally just copied the example data you provided. However, if you have a field for month or day of week or whatever you want your measure to filter by, it would go in here in place of my red text.
Here is the result of going from Seconds, to using the calculation above, to formatting it how I want it.
Now, let's say I want to display (in the hh:mm:ss format) the line graph that shows a 9 month trend. I can't do this because the hh:mm:ss data is not aggregatable.
So you are trying to show a line graph with, say, average or total time per patient aggregated by month or day or something? And you would like the formatting of the data in the line graph to be "FormattedTime"?