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 on two levels:

  1. on the product itself within it's product group
  2. on the product groups

 

So let's say my table has sales transactions with these columns:

  • Article No (Art No),
  • Article Name (Artikelname)
  • Product Group (Warengruppe)
  • Sales (Nettosumme)
  • Date (Transaktionsdatum)

When I use this formula:

 

CategoryRank_Sales = 
    RANKX(
        ALL(tblDATA[Warengruppe]),
        CALCULATE(
            SUM(tblDATA[Nettosumme]),
            ALLEXCEPT(
                tblDATA,
                tblDATA[Warengruppe],
                tblDATA[Transaktionsdatum]
            )
        ),
        ,
        DESC,
        Dense
    )

 

 

When I compare the data for two days where I know two groups switch their rank, I don't see this reflected in the output. I assume this is because I don't understand the context of the ALLEXEPT clause but I am not sure.

 

I hope someone can help basis this abstract information. But as I am sure this stems from my poor understanding of the RANKX and ALLEXCEPT formula I thought this might be sufficient.

 

Thanks!

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.

3 Replies

  • 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
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.