Custom Sorting a Slicer

[zakkair]

Hi I have a file that basically records everyday data that comes in

Column A has suppliers like "Suncor", "Shell", "IOL" (they repeat sometimes), Column B is the product such as "GAS", "DIESEL", "GAS10", "AVGAS".... then column C has volumes.

Sample would be like
Suncor, DIESEL, 10000
Suncor, GAS, 5000
Suncor, AVGAS 10000
IOL, GAS, 2000
IOL, GAS, 5000
...

When I created a powerpivot with chart and created slicers, they are ordered alphabetically. I want it such that it is ordered in a custom way. Is this possible?


Thanks in advance for the help!

 

[powerpivotpro]

In PowerPivot v2 and beyond, there is a "sort by column" feature that you can use for precisely this.

It's on the first tab of the ribbon in the PowerPivot window. You add a numeric column that is numbered appropriately and then sort the label column by that numeric column.

This is also briefly illustrated near the end of this post:

http://www.powerpivotpro.com/2011/07/powerpivot-v2-ctp3/

 

[zakkair]

I keep getting this circular dependency error when I sort by column and create unique numbers using nested ifs. Do you know why?

 

[powerpivotpro]

If those nested IF's depend on the column you are trying to sort, I think that triggers a circular error.

One trick I have found useful is to create a two-column table in normal Excel - Label and SortOrder - and number the sort order column manually or with any formulas you want.

Then copy/paste or link that table over into PowerPivot, relate it to your existing table using the Label column (make your new table the Lookup table in the relationship BTW), then use =RELATED() to fetch the SortOrder column from your new table into the existing table. Then use that new column as your SortOrder column.

 

[zakkair]

Thank you sooo much Rob! It worked perfectly. Easier too since I can number it myself!

And great book btw, a great interesting introduction to PowerPivot with enough indepth information to make a lot of my files more efficient :D

 

[powerpivotpro]

Very glad to hear it. Never gets old, always warms my heart. Thank you

 

[zakkair]

I'm running into another problem on the sort. The procedure worked for about 5 of my fields until I tried to sort it by "Supplier". I followed the exact procedures before but now I get the error "Index is out of range..." and a really long error message shaped somewhat like this. It's fine if there's no fix, but I'm just wondering if it is too much data set.

************** Exception Text **************
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.Collections.ArrayList.get_Item(Int32 index)
at System.Windows.Forms.DataGridViewColumnCollection.get_Item(Int32 index)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.widgettag_ItemsChanged(Object sender, WidgetTagEventArgs e)
at Microsoft.AnalysisServices.Common.TableWidgetTag.OnItemsChanged(TagColumn tagColumn)
at Microsoft.AnalysisServices.Common.TableWidgetTag.Add(IWidgetTagItem tagItem)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.UpdateWidgetTags()
at Microsoft.AnalysisServices.Common.TableWidgetHeaderCell.set_ColumnFilter(ColumnFilter value)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.LoadColumnFilters(TableWidgetGridColumn column)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.LoadColumnProperties(String columnName)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.InitializeGridLayout(Boolean isReset)
at Microsoft.AnalysisServices.Common.TableWidgetGridControl.SetStorageTable(ITableDataView value, Stream stream)
at Microsoft.AnalysisServices.Common.TableWidgetPanel.SetStorageTable(ITableDataView value, Stream stream)
at Microsoft.AnalysisServices.Common.SandboxEditor.InitializeTableWidget(DataModelingTable table)
at Microsoft.AnalysisServices.Common.SandboxEditor.PopulateTableWidget(String tableId)
at Microsoft.AnalysisServices.Common.SandboxEditor.sheetsTab_sheetChanged(Object sender, EventArgs e)
at Microsoft.AnalysisServices.Common.SheetTabs.SetSelectedSheetInternal(String key)
at Microsoft.AnalysisServices.Common.SheetTabs.button_Click(Object sender, EventArgs e)
at Microsoft.AnalysisServices.Common.SheetTabs.Button_MouseDown(Object sender, MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.RaiseMouseEvent(Object key, MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseDown(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseDown(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseDown(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
....