Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Troubles with RANKX

Hi,   Still not very experienced with Power BI I am having troubles with understanding the correct usage of RANKX. I want to get a measure which ranks my products by sales. But I want to do this o...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    ALLEXCEPT dax: Removes all context filters in the table except filters that have been applied to the specified columns.

    RANKX dax: Returns the ranking of a number in a list of numbers for each row in the table argument.

    I created a sample to help you better know about these two dax .

    Original data :

    Measure 3 = RANKX(ALLEXCEPT('Table (2)','Table (2)'[Name]),CALCULATE(SUM('Table (2)'[Value])),,DESC,Dense)

    First group by 'Table (2)'[Name], and then sort within the group .

    The effect is as shown:

    More info about RANKX and ALLEXCEPT can refer to these links:

    https://docs.microsoft.com/en-us/dax/rankx-function-dax

    https://docs.microsoft.com/en-us/dax/allexcept-function-dax

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Try like

 

CategoryRank_Sales =
RANKX(
filter( ALLselected(tblDATA[Warengruppe],tblDATA[Transaktionsdatum]),
tblDATA[Transaktionsdatum] = max(tblDATA[Transaktionsdatum]))
CALCULATE(
SUM(tblDATA[Nettosumme])
),
,
DESC,
Dense
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for your help.

    After applying your formula I get a rank with one error. Please see my screenshot:

     

     

    This is the formula I entered:

    CategoryRank_Sales = 
    RANKX(
        FILTER(
            ALLSELECTED(
                tblDATA[Warengruppe],
                tblDATA[Transaktionsdatum]
            ),
            tblDATA[Transaktionsdatum] = [SelectedDate]
        ),
        CALCULATE(
            SUM(tblDATA[Nettosumme])
        ),
        ,
        DESC,
        Dense
    )