RankX

[Cyriackpazhe]

Why am i getting the output like this

[techies]

pls try this - 

Measure =

VAR totalamount = [Enrollment Amount]

VAR QuarterRank =

    IF (

        ISINSCOPE ( 'DATE'[quarter]),

       RANKX (

            ALLSELECTED ( 'DATE'[quarter] ),

            [Enrollment Amount],

            ,

            DESC,

            DENSE

        )

    )

VAR Result =

    IF (

        NOT ISBLANK ( totalamount ),

        QuarterRank

    )

RETURN

    Result

[bhanu_gautam]

@Cyriackpazhe 

The issue you're encountering with the RANKX function in Power BI is likely due to the way the function is being used in conjunction with the ALL function. The ALL function removes all filters from the specified columns or tables, which can cause the RANKX function to rank all values as 1 because it is not considering any context for the ranking.

To fix this, you need to ensure that the RANKX function is applied in a context that allows it to rank the values correctly. 

DAX
RankX = RANKX(
ALL('Calendar'[Date], 'Calendar'[Quarter]),
[Total],
,
DESC,
DENSE
)

[Cyriackpazhe]

I'm still getting incorrect result.Also if we do as you mentioned, won't the virtual table contain all possible combinations of year and quarter. The rank would be assigned as per its position in this entire set right

[Anonymous]

Hi @Cyriackpazhe , hello bhanu_gautam, thank you for your prompt reply!

Please try the following measure:

MeasureTotal = SUM('Table'[Total])

RankX = 
IF (
    ISINSCOPE ( 'Table'[Date].[Quarter] ),
    RANKX (
        FILTER (
             ALL('Table') ,
            YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
        ),[MeasureTotal],,DESC
                ,
        DENSE
    ),
    IF (
        ISINSCOPE ( 'Table'[Date].[Year] ),
        RANKX (
            ALL ( 'Table' ),
            CALCULATE (
                SUM ( 'Table'[Total] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Year] )
            ),
            [MeasureTotal],
            DESC,
            DENSE
        ),
        BLANK ()
    )
)

Result:

Best regards,

Joyce

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

[Cyriackpazhe]

but why isn't the meausre i wrote working. It should be correct right

[Anonymous]

Hi @Cyriackpazhe  , thank you for your feedback.

If possible, please update the sample pbix file without any sensitive information for further troubleshooting.