Power BI
May 20, 2016
Hi
I have a PowerPoint table that shows weekly (column) sales (value) by Item Num (rows). I have created table relationships and displaying the Item Name connected to the Item Num next to the Item Num in tabular format.
The challenge I have now is two parts.
1) Sorting can no longer be done on an individual column data due to the tabular format. If I remove the Item Name - sorting is permitted on the individual columns.
2) Could someone help me with a Macro which I believe is the only way - where the last column (latest week) in the PowerPivot Table is the column that is sorted descending value. (not the Grand Total).
Look forward to any help,
all the best,
- Mike
July 16, 2010
Hi Mike,
It's difficult to picture the layout of your data and PivotTable, but you should be able to sort on the column you want.
If you select a value cell in the column you want to sort, then select the filter drop down in the Row Label header of the PivotTable and set the sort it should sort based on the selected column.
If that doesn't work, please share a file or screenshot so we can better understand.
Mynda
Power BI
May 20, 2016
Hi Mynda
Unfortunately using the tabular format and having the item_num and item_name showing in the same row, the sort by dates does not work.
also when a refresh takes place, it no longer keeps the last column sorted and this is key for the weekly report Im looking to create.
No matter how I try and sort - right click and sort, sort via ribbon, it just will not sort -
If I remove the item_name from the pivot table then it will sort on the last column but I need the descriptive text to be shown.
Additionally when the next week data comes in , the last column is not sorted descending and this I understand can only be done by a macro...so hopefully someone in the forum can help?
Thank you for your continued support,
All the best,
- Mike
July 16, 2010
Hi Mike,
Thanks for the screenshot.
Have you tried switching the two row label column positions? I can't see what they're called but since they're in a 1:1 relationship it shouldn't matter which is first. The sort is applied to the items within each row grouping. It's difficult to explain and I'm not sure if it will work for you, but it might.
If not, please post your question in the VBA forum because it will be a VBA question, not a Power Pivot question.
Mynda
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Mike,
A sample file will clarify many things, it will be very helpful if you can upload a simplified file, with just a few rows of data.
I an only guess that you want to apply a sort operation on the last column, but you should apply the sort on the Product column!
Use "More Sort Options" from Product Name column, and you will be able to sort Descending based on the column values, you will be able to select the column you want.
Another thing that comes to my mind is the layout you chose.
Is there a reason for this layout, that will end up with hundreds of columns (at least 50 per year) ?
Another layout that can work better is to add a column to calculate the Week of the Month, you will only have 4 weeks each month, so your pivot table will have 4 values columns: Week 1, Week 2, Week 3, and Week 4. If you place the Month into rows, you will be able to slice the data to see only the months you want, and the report will be much cleaner.
Power BI
May 20, 2016
Hi Catalin
your response is very useful, thank you! The challenge I have is that although I can follow the gist of the response, I am unable to completely understand it.
The More Sort Options - does not work for me. Is this because I am using Power Query as well?
Thanks for your feedback,
- Mike
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Mike,
In the More Sort Options, there is another button: More Options 🙂
Use this button, and you will be able to pick the column where you want to apply the sort.
Here is a code that does that: identifies the last column to sort, assuming that on pivot headers row 10 you have 2 row fields and a grand total, this is the reason for deducting 3 from row 10 count:
Sub FilterLastColumn()
Dim Col As Integer
Col = Application.WorksheetFunction.CountA(Me.Rows(10)) - 3
Application.CutCopyMode = False
Me.PivotTables("Pivot_Total").PivotFields( _
"[tbl_Products].[Item_Name].[Item_Name]").AutoSort xlDescending, _
"[Measures].[Sum of Units]", Me.PivotTables("Pivot_Total"). _
PivotColumnAxis.PivotLines(Col), 1
End Sub
The rest of the code is provided by the macro recorder, I only replaced the variable column number (Col).
Power BI
May 20, 2016
Thanks Catalin
I have now updated with an additional week of data and the script now reports an error of 'subscript out of range'
As far as I know, no change to the structure was made and the code is intact from what was posted.
Any insight to the error will be appreciated,
- Mike
1 Guest(s)