Sort by column

fAdI

New member
Joined
Nov 9, 2013
Messages
11
Reaction score
0
Points
0
I'm having similar problem discussed here - I wish someone can take a look at file below - I want to sort the table by the measure in column I ? I've been trying to do it in the 'more sort options' in the area_title column but nothing seems to work. have 'sort automatically' unchecked as suggested above but still not working.

http://sdrv.ms/IGGy6C
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider

Herbds7

Banned
Joined
May 8, 2013
Messages
197
Reaction score
0
Points
0
As Ken predicted:
...as your data is currently set up,... it's going to cause you grief...
Looking at your 20MB file, I realized your tables are aggregations, by year and quarter, of some unknown database.
You are trying to aggregate an aggregation. Not what PowerPivot is designed to do.
Please disregard all my previous work.
 

fAdI

New member
Joined
Nov 9, 2013
Messages
11
Reaction score
0
Points
0
Ok. That's helpful. At least I know the sort can't be done as is and it's a data/table design problem.
I'll at least reduce the data and file size for illustration.
 

fAdI

New member
Joined
Nov 9, 2013
Messages
11
Reaction score
0
Points
0
Made some progress here and one last question when someone gets a chance: - some sorting is working but not on calculated columns, you think it’s still a data issue??

Went back to Ken’s original comment and made sure I had separate/ normalized tables for all the columns (A:M) in the data tables. It was missing a few, on top of what you started, but probably only one mattered that had different values. I think all the relationships are there now and able to do some sorting (which before I wasn’t able to do any, even the titles columns). It sorts on the Values columns, but only the ‘no calculations’ values. It doesn’t sort on a ‘difference % from’ type columns. I’m doing the sort from the first left column area_fips.
The data and relationships are seen in the PowerPivot window as I deleted the links to the spreadsheets.
A reduced 7MB size file link is below.
https://drive.google.com/file/d/0B0Aawsz25Q6BdzEwVHlYMGRUY0k/edit?usp=sharing
 

Herbds7

Banned
Joined
May 8, 2013
Messages
197
Reaction score
0
Points
0
CEW2012 and CEW2013 Tables are not in a PowerPivot acceptable format.
Delete ALL the Tables in the PowerPivot Window and start fresh.
Load data from your Database in a manner described in the chapter
"Loading Data and Model" in the book
"PowerPivot for Excel 2010" by Marco Russo and Alberto Ferrari, or the newer
"MS Excel 2013, Building Data Models with PowerPivot".
Loading requires knowledge of SQL and is best handled by your IT department.
 

fAdI

New member
Joined
Nov 9, 2013
Messages
11
Reaction score
0
Points
0
noted, thanks. i see what you mean. fields like year and quarter should be in date format for example.
 

fAdI

New member
Joined
Nov 9, 2013
Messages
11
Reaction score
0
Points
0
Seems like my problem is not about the data model and relationships but about sorting custom calculations? In below link for example, I'm pivoting one table as was suggested originally. I can not sort the "% Difference from" column. When I did a basic sort of the values with no calculation then when I try to insert a duplicate column and turn it into a custom calculation (% Difference from..), I get the message below.
"Because Autosort & Autoshow cannot be used with custom calculations that use positional references. Do you want to turn off autosort/show?"

http://sdrv.ms/JgfBHl
 
Top