Powerpivot is sorting a numeric field as text

Question

Hello there!

I'm trying to sort a numeric field on a pivot generated from Powerpivot. This field contains numbers 100, 90, 60, 30, 10, 1 or 0 which are categories on my table. But when I click "Sort Z to A" the pivot is sorted as:

 

Row Labels
90
60
30
100
10
1
0

 

In other words: the pivot is treating the numbers pulled from Powerpivot as text!

I've tried to reinstall Powerpivot, without success. No way I can make this work. Please, any help is very welcome.

Some information you may find usefull:

• I imported the DB from Access 2010 query
• The field data type in Access is correctly configured as "Double"
• The field data type in Powerpivot is correctly configured as "Whole Number"
• I reinstalled Powerpivot and it didn't work
• I'm using Powerpivot version 10.50.2500.0
• Excel 2010 version 14.0.6106.5005

Thanks for the help!

Ronaldo

Answer 1

Hi RJrN,

Even if your data are numbers, it will be treated as string data type(label not value) when drag it on the category. This is by design. Thanks for your understanding.

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi RJrN,

Even if your data are numbers, it will be treated as string data type(label not value) when drag it on the category. This is by design. Thanks for your understanding.

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Challen,

Thanks for your answer.

But I'm little confused: is this a PowerPivot design issue?

Because in a nomal pivot (when pulling data from Access for example), the behavior when I drag a numeric field on the category is different: it is treated as a normal numeric value and I can sort it out.

Please, could you clarify? Are the pivots generated through PowerPivot different than the ones generated through Excel direclty?

Any comment is very much appreciated! Thanks,

Ronaldo

Answer 3

Hi Ronaldo,

Is these value  100, 90, 60, 30, 10, 1 or 0 come from dimension or measure? could you please show us the workbook which using measures on category field in common Excel Pivot table?

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Hi Ronaldo,

Is these value  100, 90, 60, 30, 10, 1 or 0 come from dimension or measure? could you please show us the workbook which using measures on category field in common Excel Pivot table?

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

 

Hi Challen,

These values are a numeric dimension on my table. But the PowerPivot Pivot table treats it as text/alpha-numeric when ordering.

Thanks,

Ronaldo

Answer 5

Hi Ronaldo,

This behavior is by design, as Excel always sorts fields placed in the Row and Column areas alphabetically, to work around the issue, you can add another rank column behind the PowerPivot workbook, then you can refer to the steps in Boyan Penev's blog about Ordering Dimensions in PowerPivot http://www.bp-msbi.com/2010/11/ordering-dimensions-in-powerpivot/ 

For more information about this topic, you can refer to this two FAQs:

http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=94
http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=66

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

Hi Challen,

This is not an Excel behavior, as it doesn't report this way when I input the data on a spreasheet and generate the pivot directly in Excel.

Any other thoughts?

Thanks,

Ronaldo

Answer 7

Ronaldo

As you only can sort column and row labels is banding a good option to sort a measure. Ranking can also be used but is not always that easy to do. PowerPivot Denali CTP3 has now the dax functions RANK.EQ() and RANKX() which makes life easier.

Another alternative is the solution with the leading zeroes something like next formula. The column has to be used as row label to enable sorting, and the corresponding column can be hidden afterwards. But current solutions is not always practical.

=REPT("0",10-len(FLOOR([Values],1)))&[Values]

---

Eddy Nijs

Answer 8

This formula is awesome - thanks!

Answer 9

Challen,  can you please bring this up to the PowerPivot guys and let them know that this is a flaw in its design.   We are considering other reporting tools because of this.  If the values are numeric in PowerPivot, then they should be numeric (and thus sorted this way) in the Excel pivots.

Thanks,

Ron

Answer 10

Challen,  can you please bring this up to the PowerPivot guys and let them know that this is a flaw in its design.   We are considering other reporting tools because of this.  If the values are numeric in PowerPivot, then they should be numeric (and thus sorted this way) in the Excel pivots.

Thanks,

Ron

Challen,

I agree with Ron: today we're considering other reporting tools because of this. Are you escalating this with PowerPivot guys?

Thank you!

Ronaldo

Answer 11

Hi Ronaldo,

I will do more research to this issue, if this is a known issue, I will submit this to the connect for you. thanks for your partience and understanding.

---

Challen Fu

TechNet Community Support

Answer 12

I also encounter this problem.  However, the larger problem for us is with dates.  Date fields come into PP as date fields and move into Excel as text strings.  No matter what DAX format function I apply in PP, the column is still a text string once in Excel.

This means that I cannot filter at all.  I can sort if I change the date to a serial of some kind, but this is work that I should not have to do.

Answer 13

Hi Ronaldo,

I will do more research to this issue, if this is a known issue, I will submit this to the connect for you. thanks for your partience and understanding.

---

Challen Fu

TechNet Community Support

Hi Challen,

Any news here?

Regards,

Ronaldo

Answer 14

Or you can simple drag the column to value field to create a [sum of Col1] as well, then sort by [sum of Col1]. It requires no calculated column and fast, drawback is that this cannot be hidden.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 15

Challen,

Please add my vote to the chorus. Sorting numbers alphabetically is an incredibly unhelpful behavior, and adds an unneccessary layer of effort with absolutely no added value to every PowerPivot project with a numeric dimesion.  Many of your competitors' BI products support numeric sorting when appropriate, which is one of the reasons I frequently recommend them to colleagues when they ask me about PowerPivot.

Thanks!

George

---

George W. (different George W.)

Answer 16

Hello,

I am trying to sort dates in a pivot, that is based on PowerPivot data.  The powerpivot data is formatted as a date but I have found no way to sort the resulting pivot table by date.  I suppose I could go with a format of YYYYMMDD but it is by no means user friendly to read that way.

Please help, it seems crazy that date fields would sort alphabetically.

Cheers