Forum Discussion

bantoszewski's avatar
bantoszewski
Frequent Visitor
2 years ago
Solved

custom sorting in a matrix table

Hi,   I have the following table: Product Month Sales Volume A 1 50 40 A 2 60 30 A 3 40 20 B 1 40 30 B 2 30 20   which I would like to show in matrix with ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi bantoszewski 
    You can use calculate on your measure to remove the Month filter to rank the products

    Rank measure =
    RANKX(
        ALLSELECTED('Test Table'[Product])
        ,CALCULATE([SalesAm],ALLSELECTED('Test Table'[Month]))
        ,
        ,DESC
        ,Skip
    )
     
    If the year/month are from a seperate calendar table you could do AllSelected(CalendarTable) or if they are from the sale Date column use AllSelected(table[date]). If the same table different columns AllSelected(table[month],table[year]).
bantoszewski's avatar
bantoszewski
Frequent Visitor
2 years ago

I thought about bookmarks but then what about using slicers, for example on chosen products or on certain dates? As far as I'm concerned the bookmarks remebers all filters.

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years ago

Hi bantoszewski 

Bookmarks can save the settings of an entire page or single/groups of visuals:

 

When you save the bookmark only select the table and NOT the slicers.

That way it only save the table not the visuals.

 

  • bantoszewski's avatar
    bantoszewski
    Frequent Visitor
    2 years ago

    It looks like the bookmarks are gonna work fine.

    However I see my ranking function needs some modification.

    In the first example it worked fine because the sales values were what they were. If I modify them (look below) the sorting is not good.

    I need to rank Sales taking into account just total sales of Products, and the rest (mon 

     

     

    I want to add Year as well

     

    Of course it is just a sample. The real table has thousand of rows, tens of measures, etc.

    I suppose something should be done to RANKX function, for example not ALLSELECT but maybe ALLEXCEPT or mabe some FILTER inside. I tried to google it but without success yet. I am a beginner.

    • SamWiseOwl's avatar
      SamWiseOwl
      Icon for Super User rankSuper User
      2 years ago

      Hi bantoszewski 
      You can use calculate on your measure to remove the Month filter to rank the products

      Rank measure =
      RANKX(
          ALLSELECTED('Test Table'[Product])
          ,CALCULATE([SalesAm],ALLSELECTED('Test Table'[Month]))
          ,
          ,DESC
          ,Skip
      )
       
      If the year/month are from a seperate calendar table you could do AllSelected(CalendarTable) or if they are from the sale Date column use AllSelected(table[date]). If the same table different columns AllSelected(table[month],table[year]).