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 number Stock Sold Difference 1 121 89 32 2 212 200 12 3 142 105 37 4 ...
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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

mattkocak Thanks for your help. I added the two calculated columns but it seems like both columns are not needed? I sorted by Rank column and removed RankRaw. But how to show Rank in ascending order so that the color order is from red to yellow to green? Is it possible to hide the Rank column from the table?

 

mattkocak's avatar
mattkocak
Kudo Kingpin
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.