Forum Discussion
Aggregating Custom formatted numbers
Is there a way to aggregate a column that has a custom numerical format? For instance, let's say I want to display some leading zeros:
Formatted = FORMAT([Sales],"000000")
Now a Sales value of 123 has become 000123. When Sales is unformatted, I can aggregate it. Once it's formatted however, I cannot. Anyone know why this is or how to get around it?
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.
7 Replies
I'm not sure I understand fully what you are trying to do, but what if you aggregate first then custom format?
Big picture I'm trying to take a duration value in seconds and display it in the hh:mm:ss format. I've read a great deal on this, and most solutions produce a string at the end (like the solution here). I've manage to get the data into something that's very close. Here's the formula for reference:
Time = IF(MOD([Seconds],60)=60,0,MOD([Seconds],60)) + IF(MOD(INT([Seconds]/60),60)=60,0,MOD(INT([Seconds]/60),60)*100) + INT([Seconds]/3600)*10000This uses modulo to create a number that reflects time. It produces this (seconds vs time):
61 = 101
120 = 200
3600 = 10000
If I can then take this number and format it to displays as "0:00:00" then the values become:
61 = 0:01:01
120 = 0:02:00
3600 = 1:00:00
And I have seconds to hh:mm:ss. Which is where my question comes in. How do I aggregate this?
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.
