Sort by column

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

Hi Fadi,

I split this into it's own post, rather than append it to another. I've assumed that this is a PowerPivot sort by column question as well, although I can't open your file as it's too big for the Excel webapp to render. Can you post an alternate link to it?

In the mean time, you may want to read this, as it may help: http://www.excelguru.ca/content.php?252-Sorting-A-Column-Of-PowerPivot-Data-By-Another-Column

Thanks. I'm actually sorting rows by values in the column I. maybe this link would work otherwise, i'll try another.


https://drive.google.com/file/d/0B0Aawsz25Q6BR2N4NndRdFhuTDQ/edit?usp=sharing

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.

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.

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

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.

noted, thanks. i see what you mean. fields like year and quarter should be in date format for example.

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