Troubles with RANKX

[Anonymous]

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!

[Anonymous · Accepted solution]

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]

@Anonymous , Try like

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

[Anonymous]

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
    )