Aggregating Custom formatted numbers

[th3h0bb5]

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?

[dkay84_PowerBI]

I'm not sure I understand fully what you are trying to do, but what if you aggregate first then custom format?

[th3h0bb5]

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)*10000

This 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?

[th3h0bb5]

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.

[dkay84_PowerBI]

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"?

[th3h0bb5]

Yes.

[dkay84_PowerBI · Accepted solution]

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.

[rednet]

I have same problem once the format happens it no longer aggregates.

I tried your solution, I want to replace x with the currecy value such as "USD" etc.

LocalAmount = CONCATENATE("x" ,FORMAT(CALCULATE(sum(FactMultiCurrSales[LocalSalesAmount]),All(FactMultiCurrSales[Currency])),"###.##"))

I tried LocalSalesAmount with FORMAT I got the same issue.

Then, I created a measure and then applied FORMAT, but still the same issue. As soon as any FORMAT, CONCATENATE is applied the aggregation stops, as the new field is treated as "don;t summarize"

MesLocalAmount = VAR T = SUM(FactMultiCurrSales[LocalSalesAmount]) return  T