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
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
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
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
Thanks Mynda
I moved the two row label columns and unfortunately it did not make any difference - the column will not sort. I have posted a request for help in the VBA section.
thanks
- Mike
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.
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
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).
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
I'll have to see the updated file to understand what happened, please send it if possible.
