Quick Question About sorting.

[GAPER]

I have 1 level of aggregrations (0-4, just a dummy for illustration) I would like to be able to sort by the value either in the groupping (0-4) or by the individual columns (e.g. EPS_GROWTH) is there a way i can achieve that? I think using individual columns as the values is not viable because i can't do maintain the conditional foramtting one by one. Look forward to any suggestions. 

[DataNinja777 · Accepted solution]

Hi @GAPER ,

You can achieve this sorting dynamically in Power BI by using a combination of DAX measures and sorting techniques without losing conditional formatting. One way is to create a sorting measure that allows sorting based on different grouping levels or individual columns. First, create a table for sorting options using the following DAX formula:

Sort_Options = DATATABLE(
    "Sort By", STRING,
    {
        {"Grouping"},
        {"EPS_GROWTH"},
        {"Total"}
    }
)

This table serves as a slicer, allowing the user to select the sorting preference. Next, define a DAX measure that applies sorting dynamically:

Sort_Measure = 
SWITCH(
    SELECTEDVALUE(Sort_Options[Sort By]),
    "Grouping", SELECTEDVALUE(AggregationTable[GroupingColumn]),
    "EPS_GROWTH", SELECTEDVALUE(AggregationTable[EPS_GROWTH]),
    "Total", SELECTEDVALUE(AggregationTable[Total]),
    BLANK()
)

You can then apply this measure to sort the table or matrix visual. If your data has a natural hierarchy (e.g., aggregation levels 0-4 and individual metrics like EPS_GROWTH), another approach is to create a calculated column that ranks values within each level:

Rank_Column = 
RANKX(
    ALLSELECTED(AggregationTable),
    AggregationTable[EPS_GROWTH],
    ,
    DESC,
    DENSE
)

Sorting by this ranking column will order the data dynamically. If you primarily want to sort by the Total column while keeping conditional formatting intact, simply sorting the table by Total and applying conditional formatting at the row level ensures the styling remains consistent. Let me know if you need further refinements!

Best regards,

[Akash_Varuna]

Hi @GAPER , You could create a sorting column or custom column for sorting and you could use that column on your visual for sorting
If this post helped please do give a kudos and accept this as a solution 
Thanks In Advance