Forum Discussion
Sorting two columns in stacked column chart
Hi,
I'm encountering an issue which feels so simple to remedy but I've tried searching for solutions including merging columns, DAX and using a sort table but nothing seems to be working. Essentially I have a simple stack columns chart with two fields on the Y-axis - 'number of years' and 'number of month' each represented by a integer. I can sort them by year no problem but then can't get it to sort by month as the values are then just sorted alphabeticlally acording to the x-axis. This means that presentationally it orders in just year values not year+month which would show greatest lenght of time through to shortest lenght of time, which is what I want.
It's pretty frustrating as I'm sure there's a simple solution but I just can't get my head around it. I'm pretty new to Power BI.
Any help appreciated
Hi amitchandak,
I've found a way of achieving what I needed although it's very long-winded and I'm sure there's soemthing simpler. Essentially I created new columns in the query editor whereby I took the year column and multiplied it by 12 to give a monthly representation of the years and then added this to the month column using the List.Sum function. I then had a column with total project length based purely on the months. I then selected the project name in the report and selected 'sorted by column' function and sorted project name with this new column. I now have a represtation of the proejct from longest to shortest sorted by the addition of years and months as demonstrated below. I'd be interested to know of any easier way to do this but it has worker for me in this instance. Thanks for showing me the 'sort by column' function as this led me onto this solution.
4 Replies
- amitchandak
Super User
Anonymous , Year and month are on x-axis , or they are two measures ?
if they two measures you can sort on only one.
But if they are two columns on the axis, they should sort together (based on column value or sort column)
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Hi amitchandak, yeah and month are both on the x-axis and are seperate data fields. Your link the sort column has got me closer though. I clicked on proejct name (x-axis field) and sorted by Project lenght month. The image shows what I now have but I want the month to go in decending order rather than acending. This way I will be able to sort the project length from longest to shortest i.e. 5 years, 5 years 3months, 4 years, 4 years 3 months etc. At the moment becasue the month value is acending it's not the right order. The Sort by column fucntion doesn't seem to have a 'decending' sort - do you know of any way around this?
Thanks for your help
Hi amitchandak, thanks for the reply. They're two fields on the Y-axis stacked on top of each other with another field on the X. Essentially I just want to sort them by a heirarchy with project year as first sort field then project month. At the moment I can either sort by the month or year but not both togher (in the same what in the table I'd sort one column then hold shift and sort another column so they follow one another). is that really not possible in a visualisation? The image below shows the output. So the light blue column is years and the dark blue is months. You can see I have sorted it by years but the months column is not currently considered and it just defaults to the x-axis field?
Hi amitchandak,
I've found a way of achieving what I needed although it's very long-winded and I'm sure there's soemthing simpler. Essentially I created new columns in the query editor whereby I took the year column and multiplied it by 12 to give a monthly representation of the years and then added this to the month column using the List.Sum function. I then had a column with total project length based purely on the months. I then selected the project name in the report and selected 'sorted by column' function and sorted project name with this new column. I now have a represtation of the proejct from longest to shortest sorted by the addition of years and months as demonstrated below. I'd be interested to know of any easier way to do this but it has worker for me in this instance. Thanks for showing me the 'sort by column' function as this led me onto this solution.
