Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Sorting years in matrix rows

    Posted Jun 30, 2021 05:37 AM

    I have a matrix for sales amount where months are column headers and sales amount is in values. Also I have Product category and year in the rows so I can expand and collapse each category. The problem is when I try to sort the matrix by a Sales amount value, years in rows also get sorted so for some categories it goes 2021 - 2019 - 2020, depending on the value. I need to keep years in ascending order and only sort product categories, just like it is possible in pivot tables in excel.

    Is it even possible with power BI matrix and how to achieve it?



    ------------------------------
    Milos Acimovic
    ------------------------------


  • 2.  RE: Sorting years in matrix rows

    Top Contributor
    Posted Aug 29, 2021 05:23 AM
    Hello,

    Effectively, not that easy :) I achieved this by adding a ranking formula. The problem is, you need to have this formula in your matrix to be able to sort data by this new measure. But it's a workaround as another :)

    1. Add the measure: Rank Product = RANKX(ALL(Product[Product]), [Total Sales]),
    2. Add the measure in your matrix,
    3. Sort the matrix by the new measure,
    4. Mask the column in the matrix,
    It keeps the year in order and sorts only products within the year.

    I hope it will help you,



      ------------------------------
      Jonathan CHANAL
      Chef de Projet Digital / Data Analyst
      MCSA Power BI & Excel
      ------------------------------



    1. 3.  RE: Sorting years in matrix rows

      Posted Mar 19, 2022 06:09 AM

      Hi Jonathan,

      Sorry for the pretty late reply,

      I applied your solution and it works great, you just have to add ALLEXCEPT(Dates[Year]) to make it work. Thank you very much for your help! :)

      I hope Microsoft will make it possible some time soon...

      Cheers!



      ------------------------------
      Milos Acimovic
      ------------------------------



    2. 4.  RE: Sorting years in matrix rows

      Posted May 24, 2022 04:39 PM
      Hello Milos,

      Can you post your actual measure formula for this. I am trying to do the same thing but I can't get it to work. Thanks.

      ------------------------------
      Jerry Galvez
      Performance Analyst
      ------------------------------



    3. 5.  RE: Sorting years in matrix rows

      Posted May 24, 2022 05:13 PM

      Hello Jerry,

      The measure is like this, but it could be different in your case. First it checks if there is only one year in a row and if there is sales for that year. If that is true it returns value of 1. If it is not true it ranks categories based on sales amount but ignoring years as a filter so it takes into account only total values for all the years. To make it work in matrix you have to use both this measure and sales amount and than sort matrix by rank. Also turn off text wrap column header and values, and than hide rank column. Looks weird but it does the job. :) I hope I was able to help you.


      IF(
       HASONEVALUE( Dates[Year] ),
       IF(
       ISBLANK( [Sales Amount] ),
       BLANK(),
       1
       ),
       IF(
       ISBLANK( [Sales Amount] ),
       BLANK(),
       RANKX(
       ALLSELECTED( 'Categories'[Category] ),
       CALCULATE(
       [Sales Amount]
       ,ALLEXCEPT( Dates, Dates[Years] )
       )
       ,,
       ASC, 
       Dense 
       )
       )
      )


      ------------------------------
      Milos Acimovic
      ------------------------------

      ------------------------------
      Milos Acimovic
      ------------------------------



    4. 6.  RE: Sorting years in matrix rows

      Posted May 24, 2022 09:23 PM

      You are a Genius! With a little bit of modification it worked, thank you so much for your time and knowledge.



      ------------------------------
      Jerry Galvez
      Performance Analyst
      ------------------------------



    5. 7.  RE: Sorting years in matrix rows

      Posted May 25, 2022 03:02 AM
      I am glad I was able to help :) And its @Jonathan CHANAL that actually showed me the trick :)​​​

      ------------------------------
      Milos Acimovic
      ------------------------------



    6. 8.  RE: Sorting years in matrix rows

      Posted May 27, 2022 11:14 AM
      Yes, thank you  @Jonathan CHANAL. I thought I had it correctly but I just realized because I have several levels to drill down every level gets sorted differently and not by highest total sales. It would be as if you had another 4 levels between your Category and Years.

      ------------------------------
      Jerry Galvez
      Performance Analyst
      ------------------------------