Hello,
I’m trying to sort the ‘CycleText’ row in descending order (Current date at the beginning of the table. Really appreciate any help.
Sort Order.pbix (115.0 KB)
Thank you,
Nipuna
Hi @nipunadv,
First you can clean your data by removing blanks from the calendar(2) table. You have more than 1800 rows as blank. After that you can short the column CycleText by PeriodID
- Goto Data tab. Select Calendar(2)
- Select the CycleText column and then select Sort by Column as shown below.
Thanks
Mukesh
@nipunadv
To get it working, you first have to sort the column CycleText in Descending Order in Power Query and then you have to Add Index Column. The default behavior of Column sorting is that it always sorts from lowest to highest i.e. Ascending. What we have done is we have assigned the lowest Index to the Latest (Highest Cycle Text).
Then Add the Index Column to the COLUMNS section in Fields on top of CycleText & CycleWeekText. Now the columns are automatically sorted.
Now you can’t remove Index from Columns Field otherwise the Sort order will be reversed. To hide Index Column, insert Text,Shape,Or Blank Button and make it the same color as Background and put it on top of Index Column. Then click on the Text Box (I am using the Text Box), go to General section and turn on the Maintain Layer Order.
Now Bring the Text Box Forward & to Front and then Group it with the visual.
Attaching the PBIX and Source Files. I cleaned the data from Calendar as it had many Blank Rows as @MK3010 mentioned.
Let us know if it works.
Calendar (2).csv (51.3 KB)
Sales (2).csv (250.8 KB)
Sort Order.pbix (126.3 KB)
Thanks.
2 Likes
Thank you so much for your response.
This does help with the issue but it also adds couple of extra columns. See below.
This is how it looks normally.
Thank you so much for your response. While I was trying to anonymized Data, I lost many rows.
I tried this already and I get this error message.
You can copy the visual and then turn off the subtotals at Column level in the original visual. Now, you can put the duplicated visual at the back of the original visual just showing the total column and hiding the rest.
You can of course make it as neat as you can.
Sort Order.pbix (127.4 KB)
Thanks.
I see what you are saying but I need to have subtotals at column level. As you can see below. It’s like total for four weeks and it goes on for every month…
This error is because you have empty cell in the CycleID column. Try having unique value.
@nipunadv
Under Subtotals, Turn on PER COLUMN LEVEL then Turn off Index and Cycle Text and then
Voila!!!
1 Like
Yah, that works on the file I sent you. I lost data while I was trying to anonymized data. Please look at this new file. It gives a much better idea of what I am working with. Sort Order v2.pbix (914.0 KB)
Again, thank you so much for all the responses. 
you can simply convert your matrix in table and then you can short the latest cycle text to first .
Note : I can see there ,Data type of period is different in between the sales & calendar table.
Kindly make sure to use same data type .
currently period id in calendar is date type and period id for sales table is whole number.
or you can use @MudassirAli @MK3010 their way as well by adding index in power query.
Thank you
I cannot have multiple total columns.
I can have only one total column (highlighted in yellow) and also notice the order of 2020-C06W1, 2020-C06W2, 2020-C06W3 and so on (highlighted in orange). I only need to change the order to be like 2020-C07, 2020-C06, 2020-C05, etc. I’m trying to mimic an excel file for the upper management.
Thanks for your response. I’m trying to mimic an excel file for the upper management so this doesn’t work.