Forum Discussion
dvhez
7 years agoHelper II
Sort values (rows) by categories (e.g. months)
Data looks like:
Year Month Value 2018 Jan 7 2018 Jan 1 2018 Jan 5 2018 Jan 9 2018 Jan 2 2018 Jan 4 2018 Feb 6 2018 Feb 2 2018 Feb 9 2018 Feb 4 2018 Feb 8 2018 Feb 1
Is there a way to sort this table as follows? (Lowest to highest, keeping month order)
2018 Jan 1 2018 Jan 2 2018 Jan 4 2018 Jan 5 2018 Jan 7 2018 Jan 9 2018 Feb 1 2018 Feb 2 2018 Feb 4 2018 Feb 6 2018 Feb 8 2018 Feb 9
My objective is to average the lowest 2 values, then the next 2 and so on... but maintaining their categories (months)
2018 Jan 1.5 Bottom 2 2018 Jan 4.5 Mid 2 2018 Jan 8 Top 2 2018 Feb 1.5 Bottom 2 2018 Feb 5 Mid 2 2018 Feb 8.5 Top 2
Thanks in advance!
You may add the following calculated columns.
Column = RANKX ( FILTER ( Table1, Table1[Year] = EARLIER ( Table1[Year] ) && Table1[Month] = EARLIER ( Table1[Month] ) ), Table1[Value], , ASC, DENSE )Column 2 = DIVIDE ( EVEN ( Table1[Column] ), 2 )
1 Reply
- v-chuncz-msftCommunity Support
You may add the following calculated columns.
Column = RANKX ( FILTER ( Table1, Table1[Year] = EARLIER ( Table1[Year] ) && Table1[Month] = EARLIER ( Table1[Month] ) ), Table1[Value], , ASC, DENSE )Column 2 = DIVIDE ( EVEN ( Table1[Column] ), 2 )