cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
th3h0bb5
Resolver II
Resolver II

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?

1 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.

View solution in original post

7 REPLIES 7
dkay84_PowerBI
Microsoft
Microsoft

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

Here is the result of going from Seconds, to using the calculation above, to formatting it how I want it.

Time.png

 

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

Yes.

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.

 

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

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors