Sorting Groups in Power BI

[Lauren_Michelle]

Hey everyone, 

I have a complex sort order issue in Power BI. I have a huge amount of sales/volume data related to specific promotional periods - 17 periods in a year, roughly 3 weeks in length, with one 4 week period. The data is daily.

My products are split into 2 divisions (Grocery and Ice Cream), each division has their own promo cycle. My manager wants to be able to see Sales Value/Volume by promo period in a bar graph. I have set up each division on their own sheet and applied a filter to show the relevant Val/Vol data.

I used the date field (the column in my source table which contains the daily dates) in Power BI to apply grouping to the specific dates, so that they are grouped by period. I simple did this process twice to create different grouping for each division and applied the related grouping measure to each bar chart. 

As the groups are not sorting numerically, the periods are obviously coming up in a random order (1,10,11, etc). I need them to sort in the correct order as they are date fields.

I was going to sort using the date field, however as I have 2 different groupings, there is obviously crossover on many of the dates, because they are grouped differently based on what division they are e.g. 25th of July is P9 for Ice Cream, but P10 for Grocery.

I want to avoid splitting out my table, as I am afraid this will complicate total topline reports which look at overall business and how each division performs side by side.

[#2 · Accepted solution]

Issue resolved - I created a separate table in Excel with the Period Name (e.g. P1 2021) and corresponding sort order.

I then loaded the table into Power BI, made a relationship between the 'Period Name' in my sorting table and the period name column assigned to my main database by the grouping function and created a new Column using the 'RELATED' function, which allocated the correct sort number to the corresponding period for every row.

Then, when selecting the relevant grouping field, I did a 'Sort By' based on the new Column and it works perfectly.

I simply applied the same 'Sort By' on the relevant grouping field measure (Ice Cream & Grocery) and it sorted the relevant periods in the correct order.