Aug 28 2021 07:11 AM
Hi, I have multiple data in this order as you see below:
Stories |
1.1 Story |
1.2 Story |
2.1 Story |
3.1 Story |
3.2 Story |
4.1 Story |
4.2 Story |
4.3 Story |
4.4 Story |
4.5 Story |
4.6 Story |
4.7 Story |
4.8 Story |
5.1 Story |
6.1 Story |
6.2 Story |
6.3 Story |
7.1 Story |
8.1 Story |
9.1 Story |
9.2 Story |
9.3 Story |
9.4 Story |
9.5 Story |
9.6 Story |
9.7 Story |
9.8 Story |
9.9 Story |
10.1 Story |
10.2 Story |
10.3 Story |
10.4 Story |
10.5 Story |
10.6 Story |
11.1 Story |
11.2 Story |
12.1 Story |
13.1 Story |
But when I take it to slicer, I see something like this:
even if the slicer is set to sort data from source order. I don't understand why, please guide me on how to fix this.
Thanks a lot
Aug 28 2021 07:45 AM
If that's structured table slicer you may change sorting order by adding custom list as here How To Customize The Order Of Your Excel Slicer Buttons — TheSpreadsheetGuru , don't think that's feasible in this case.
It that's PivotTable slicer you may add helper column in data model with stories numbers and sort stories by these numbers.
Otherwise it is sorted as source data will be sorted, i.e. as texts.
Aug 28 2021 10:28 AM
Aug 28 2021 12:21 PM
First you need add data to data model, check this setting creating PivotTable
If add Stories to it now it'll be like
Open PowerPivot and add another column Story to the table
add storyNumber column, right click on it and Hide from Client Tools
Select Story column, Sort by Column and use stotyNumber
Hide Stories column.
Now you may use Story column and slicer on it
Please check in attached.
Aug 28 2021 12:25 PM
Sorry, formula for storyNumber column is
=LEFT( Table1[Stories], FIND(" ", Table1[Stories]) -1 ) + 0
didn't update screenshot in previous post.