How to format numbers in PowerPivot/DAX/Excel

Question

So I am using PowerPivot and have grouped a number column by buckets of 30. My only problem is that the end result doesn't sort correctly because it thinks '30' is greater than '130' based off the first character. I am trying to figure out how to format so this sorts correctly. My thought is to add a '0' in front of the 2 digit numbers since the data will max out at 3 digits. If I add a zero to the formula below it still doesn't not display the '0'. I have googled this for about an hour and no luck. Does anyone know the =format for DAX to get my buckets to sort?

=IF([Days in Q]<30,"<"&30,FLOOR([Days in Q],30)&" - "&CEILING([Days in Q],30)+IF(MOD([Days in Q],30)>0,-1,30-1))

the output looks like this:

Row Labels  
<30 
120 - 149   
150 - 179
30 - 59 
60 - 89 
90 - 119

but needs to look like this:

Row Labels  
<30 
30 - 59 
60 - 89 
90 - 119    
120 - 149   
150 - 179

thanks in advance as this site has saved me more than one occasion. I think an excel format would be close even since this is PowerPivot / DAX. I have also tried to sort this in excel and no joy.

mmarie mmarie · Accepted Answer · 2014-05-26T14:23:28

Add a sorting column called bucketsort in your table in Power Pivot. If the column containing the buckets is called buckets, the formula for this calculated column could be:

=if([Days in Q] < 30, 1, if([Days in Q] < 60, 2, if([Days in Q] <90, 3, 
    if([Days in Q] <120, 4, if([Days in Q] <150, 5, if([Days in Q] <180, 6, 7))))))

Right click the [bucketsort] column and choose Hide From Client Tools. Then go to your [buckets] column and set the order by column to the [bucketsort] column.

How to format numbers in PowerPivot/DAX/Excel

1 Answers