Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors