cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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 846 845 1 5 552 500 52 6 231 31 200 7 514 51 463 8 874 551 323 9 548 84 464 10 152 54 98 11 516 156 360 12 614 614 0

``````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.

1 ACCEPTED SOLUTION
Kudo Kingpin

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
)
)

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

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

7 REPLIES 7
Anonymous
Not applicable

@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

Kudo Kingpin

@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
Not applicable

@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?

Kudo Kingpin

Thanks for the additional info, that clears the goal up a bit. So you will have to create two calculated columns for this. One is just used as an intermediary step (RankRaw), the other is to display/sort the actual rank in the table (Rank).

Below is the DAX for the calculated columns that I had created. Note that the display rules in the pbix that you provided (red, yellow, green) were the opposite of your orignal post. I corrected this in my calculations.

``````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
)
)

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

RETURN (1000000 - documentId) + statusAdjustment``````

``````Rank =
RANKX(
'Data Table',
'Data Table'[RankRaw],
,
,
Dense
)``````

Result:

Adjustments to the DAX can be done depending on the rules that you want to use to rank your rows. Let me know if this accomplishes what you are hoping or if I can help in another way.

Anonymous
Not applicable

@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?

Kudo Kingpin

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
)
)

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

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

Kudo Kingpin

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors