Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
3 years ago
Solved

Sorting based on last column

Hi Guys,

 

Need your support please.

I have the following matrix, How can I sort the table from largest to smallest based on last day values?

 

 

Thank you

 

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi abukapsoun 

     

    This is my test table:

     

    Please create a measure:

     

    Measure = 
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
var _sum= CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=_maxdate))
return
IF(HASONEVALUE('Table'[Date]),SUM('Table'[Value]),_sum)

     

     

    Create a matrix visual. The column subtotals will become the last day value as below shown, then you can sort from largest to smallest based on last day values:

     

    If you don't want to show column subtotals, you can switch off column subtotals after sorting.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    Hi abukapsoun ,

    I think that it is not possible to sort a column in a matrix, but it is possible to sort a measure in a matrix. You have to find the way to create a measure only for the last day.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi abukapsoun 

     

    This is my test table:

     

    Please create a measure:

     

    Measure = 
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
var _sum= CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=_maxdate))
return
IF(HASONEVALUE('Table'[Date]),SUM('Table'[Value]),_sum)

     

     

    Create a matrix visual. The column subtotals will become the last day value as below shown, then you can sort from largest to smallest based on last day values:

     

    If you don't want to show column subtotals, you can switch off column subtotals after sorting.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Ania26's avatar
      Ania26
      Helper IV
      1 year ago

      Hello, for this measure, if last column has no value then it takes value from previous column. How to adjust measure so if there is no value , there is no value?