Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sort conditional formatted column

Hello,

I have the following table with a conditional formatting in the "Difference" column.

 

Week numberStockSoldDifference
11218932
221220012
314210537
48468451
555250052
623131200
751451463
8874551323
954884464
101525498
11516156360
126146140

 

 

Icon flag = 
VAR _Threshold =
    DIVIDE ( SUM ( 'Product'[Sold] ), SUM ( 'Product'[Stock] ) )
RETURN
    SWITCH ( TRUE (), 
_Threshold < 0.5, 1, 
_Threshold < 0.9, 2, 
3 )

 

 

 

Here is the table visualization but i am trying to sort by colour, from red (3) to yellow (2) to green (1). Is that possible? Any help is much appreciated.

 

Need Help
  • mattkocak's avatar
    mattkocak
    4 years ago

    Try replacing the DAX for 'RankRaw' with the following code:

     

    RankRaw = 
VAR documentId = 'Data Table'[Document ID]

VAR _Threshold = 
    DIVIDE(
        CALCULATE(
            SUM('Data Table'[Difference]),
            ALL('Data Table'),
            'Data Table'[Document ID] = documentId
        ),
        CALCULATE(
            SUM('Data Table'[Stock amount]),
            ALL('Data Table'),
            'Data Table'[Document ID] = documentId
        )
    )

VAR statusAdjustment =
    SWITCH(
        TRUE(),
         _Threshold < 0.5, 10000000,
         _Threshold < 0.9, 1000000,
        0
    )

RETURN documentId + statusAdjustment

     

    Yes, you will need to display the 'Rank' column if you want to sort based on it.

7 Replies

  • mattkocak's avatar
    mattkocak
    Kudo Kingpin
    4 years ago

    You can do this by selecting the 'Difference' column in the fields menu. Then in the Column tools ribbon, choose to sort by the column 'Icon flag'. Now when you sort your table based on the 'Difference' column, it will actually be sorted based on the 'Icon flag' column.

     

    I demonstrate this in the screenshot below by having the sort order of 'b' be based on column 'a'. Now sorting the table based on 'b' will actually display the sort order based on 'a'.

     

     

    Let me know if I can help clarify anything. If this post was helpful, please consider giving it a thumbs up and marking it as a solution!

     

    Best,

    Matt

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    mattkocak Thanks for your reply! The "Difference column" is actually a measure and when i select it in the Fields menu, there is no "Column Tools".

    Difference = Stock - Sold

    • mattkocak's avatar
      mattkocak
      Kudo Kingpin
      4 years ago

      Anonymous, ahh I see. In that case I feel like you have two options.

       

      1. Use calculated columns instead of measures

       

      2. Add the 'Icon flag' measure directly to the column and sort the table based on this. You could change its name in the visual to 'Status' or something similar that would make it appear a little more meaningful

       

      Let me know if these solutions seem adequate or if I can help further.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        mattkocak To make the problem simpler, i have removed the calculated columns and left only the essential table and columns. But i am still facing the same issue - i am trying to sort all the icons in the "Difference" column, from red to orange to green, in that order whilst keeping the "Document ID" number in ascending order for similar icon flag colours. Is this possible?

         

        There is no "Icon flag" in the list below - i think it's because it's a measure?

         

        I have uploaded my PBIX file: https://drive.google.com/file/d/12ZQ3pH39k5f3YYceEb0rPviByJRGXRBO/view?usp=sharing