Sort by measure in a matrix (Field parameter

[Varshini1234567]

Hi Team,

I have a problem, I am trying to sort based upon the measure the hierarchy in matrix should be sorted in descending order

Measure 

plan= var LV = SELECTEDVALUE(RowMatrix[slicer column])

 return

 CALCULATE(

    sum(Raw_data[Sum of Value]),

    Raw_data[plan]=LV,

    Raw_data[Date (Year)]=FORMAT(YEAR(TODAY()),""))

I have a matrix which used field paramter as hierarchy in matrix , the field parameter is created as we had a condition to sort data less than 10 to have row named others , so a new column is created named pg budget , pg mtp, pg rbu1,pg rbu2 from sum of value of raw data. slicer column is used field parameter is the slicer used in report

in my raw data the scenario column contains 

Actual

Budget

MTP

RBU1

RBU2

Now when im trying to sort the matrix based upon the rankx 

rank = var nonblank = FILTER(

            ALL(Raw_Data),

            [Plan_3pages] <> BLANK() &&

            Raw_Data[Scenario] in {"Budget","MTP","RBU1","RBU2"}

        )

        RETURN

SWITCH(

    TRUE(),

    // Case: Project Name is visible in the matrix

    ISINSCOPE(Raw_Data[Project Name]),

        IF(

            [Plan_3pages]<>BLANK(),

            RANKX(

                nonblank,

                [Plan_3pages],

                ,

                DESC,

                DENSE

            ),

            BLANK()

        ),

    // Budget

    SELECTEDVALUE(RowMatrix[slicer column]) = "Budget",

        IF(ISINSCOPE(Raw_Data[pg Budget])&&

            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg Budget]), "Others"),

            999,

            IF(

                [Plan_3pages] <> BLANK(),

                RANKX(

                    ALLSELECTED(Raw_Data[pg Budget]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ) * RANKX(

                    ALLSELECTED(Raw_Data[SET Area]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ),

                BLANK()

            )

        ),

    // MTP

    SELECTEDVALUE(RowMatrix[slicer column]) = "MTP",

        IF(ISINSCOPE(Raw_Data[pg MTP])&&

            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg MTP]), "Others"),

            999,

            IF(

                [Plan_3pages] <> BLANK(),

                RANKX(

                    ALLSELECTED(Raw_Data[pg MTP]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ) * RANKX(

                    ALLSELECTED(Raw_Data[SET Area]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ),

                BLANK()

            )

        ),

    SELECTEDVALUE(RowMatrix[slicer column]) = "RBU1",

        IF(ISINSCOPE(Raw_Data[pg rbu1])&&

            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu1]), "Others"),

            999,

            IF(

                [Plan_3pages] <> BLANK(),

                RANKX(

                    ALLSELECTED(Raw_Data[pg rbu1]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ) * RANKX(

                    ALLSELECTED(Raw_Data[SET Area]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ),

                BLANK()

            )

        ),

    SELECTEDVALUE(RowMatrix[slicer column]) = "RBU2",

        IF(ISINSCOPE(Raw_Data[pg RBU2])&&

            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu2]), "Others"),

            999,

            IF(

                [Plan_3pages] <> BLANK(),

                RANKX(

                    ALLSELECTED(Raw_Data[pg rbu2]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ) * RANKX(

                    ALLSELECTED(Raw_Data[SET Area]),

                    [Plan_3pages],

                    ,

                    DESC,

                    DENSE

                ),

                BLANK()

            )

        ),

    BLANK()

) the value gets sorted correctly in set area and budget/mtp/rbu1/rbu2 level but if i sort project name then blank value comes 1st , if i add any value like 99 to blank values then actual which is no were usedin slicer also gets filtered, even if i filter excluding actual i don't get it? how to solve the problem? is there any other approach to this

[ajaybabuinturi]

Hi @Varshini1234567,
I believe you need to tweak the DAX logic. Could you please use below DAX logic to get non blank projects

Rank = 
Var LV = SELECTEDVALUE(RowMatrix[slicer column]) 
var nonblank = FILTER(
            ALL(Raw_Data),
            NOT(ISBLANK([Plan_3pages]))&& 
            Raw_Data[Scenario] = LV &&
            Raw_Data[Project Name] <> BLANK()
        )
        RETURN
SWITCH(
    TRUE(),

    // Case: Project Name is visible in the matrix
    ISINSCOPE(Raw_Data[Project Name]),
        IF(
            ISBLANK([Plan_3pages]), BLANK(),
            RANKX(
                nonblank,
                [Plan_3pages],
                ,
                DESC,
                DENSE
            ),
            BLANK()
        ),

    // Budget
    LV = "Budget", && ISINSCOPE(Raw_Data[pg Budget]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg Budget]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg Budget]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    // MTP
    LV = "MTP" && ISINSCOPE(Raw_Data[pg MTP]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg MTP]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg MTP]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    LV = "RBU1" && ISINSCOPE(Raw_Data[pg rbu1]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu1]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu1]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),


    LV = "RBU2" && ISINSCOPE(Raw_Data[pg rbu2]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu2]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu2]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    BLANK()
) 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[freginier]

Hi, 

You can fix the sorting and blank issue with two key changes:

1. Exclude 'Actual' rows directly in your measure using:

   Raw_Data[Scenario] IN { "Budget", "MTP", "RBU1", "RBU2" }

2. Avoid ranking blank values to prevent them from appearing first:

   IF([Plan_3pages] = BLANK(), BLANK(), RANKX(...))

Also, don’t use ALLSELECTED without filtering, as it brings back 'Actual'. Use FILTER(...) inside RANKX to exclude blanks and "Others" cleanly.

freginier