ranking formula optimization

Hi,

Thanks for the solution grazitti_sapna , Kedar_Pande  and dharmendars007  offered, and i want to offer some more information for user to refer to.

hello Hemant_Jaiswar , based on your descroption, you can try the following measure.

Ranking =
VAR _topCategory =
    RANKX (
        ALLSELECTED ( dwdim_customer[CustomerName] ),
        -- Remove context for CustomerName
        CALCULATE (
            [CY #Value NOS],
            removefilter ( Dim_Date[Date] )
        ),
        ,
        -- No tie-breaking expression
        DESC -- Rank in descending order for top N
    )
VAR _bottomCategory =
    RANKX (
        ALLSELECTED ( dwdim_customer[CustomerName] ),
        -- Remove context for CustomerName
        CALCULATE (
            [CY #Value NOS],
            removefilter ( Dim_Date[Date] )
        ),
        ,
        -- No tie-breaking expression
        ASC -- Rank in ascending order for bottom N
    )
VAR _ranking =
    IF (
        SELECTEDVALUE ( 'Top OR Bottom'[Value] ) = "Top",
        _topCategory,
        _bottomCategory
    )
VAR _TOPN =
    SELECTEDVALUE ( 'Top N Parameter'[Top N Parameter], 5 ) -- Use the parameter value (default to 5 if not selected)
RETURN
    IF (
        _ranking <= _TOPN,
        -- Check if the rank is within the top N or bottom N range
        [CY #Value NOS],
        BLANK () -- Return the measure if within rank
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.