RANKX affected by item id when using ALL()

[HappyZuil]

Goal: I have a table with all of the products from multiple stores, and I'm ranking them using a mix of RANKX calculations for different meassures (Total Sales and Total Items Sold). When a particular store is selected, the rank behaves as expected. The problem occours when multiple stores are selected.

Context: This is the dimension for "Articulo"

Example: here the rank is fine. There are no gaps, and no duplicate values. 

If I select both stores then the rank doesn't work as intended.

My suspicion is that it has to do with ALL(Articulo). Here is the formula I use for this calculation:

Rank articulo basado en cantidad y venta = 
    RANKX (
        ALL(Articulo),
        RANKX (
             ALL(Articulo)
            ,[total ventas sin iva],
            ,DESC
        )    
        + DIVIDE (
            RANKX ( 
                ALL(Articulo)
                ,[Cantidad total vendida],
                ,desc
            ),
            (COUNTROWS 
                (CALCULATETABLE(
                     ALL(Articulo)
                ) )
            + 1 )
        ),
        ,
        ASC
        ,Dense
    )

Why do I use All(Articulo) instead of All(Articulo[articulo])? Because If I filter by Articulo[proveedor] then the rank is affected by this filter. Test using ALL(Articulo[articulo]):

Rank articulo basado en cantidad y venta = 
    RANKX (
        ALL(Articulo[articulo]),
        RANKX (
             ALL(Articulo[articulo])
            ,[total ventas sin iva],
            ,DESC
        )    
        + DIVIDE (
            RANKX ( 
                ALL(Articulo[articulo])
                ,[Cantidad total vendida],
                ,desc
            ),
            (COUNTROWS 
                (CALCULATETABLE(
                     ALL(Articulo[articulo])
                ) )
            + 1 )
        ),
        ,
        ASC
        ,Dense
    )

Works fine when all stores selected, but the rank changes when filtered by Articulo[proveedor].

Any hints? Thanks a lot.

[parry2k]

@HappyZuil use ALLSELECTED() instead of ALL()

[HappyZuil]

Using ALLSELECTED(Articulo[articulo]) yield the same result as using ALL(Articulo[articulo]), which is what I'm trying to avoid: it recalculates the rank when filtered by Articulo[proveedor]. In this picture, ALL() is closer to what I want to achieve.

[Anonymous · Accepted solution]

Hi,

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

hello @HappyZuil , based on your description, you can create a rank calcukated column in your demional table so that it will not change, 

In Articulo, creaet a Sum column.

Sum =
CALCULATE ( [total ventas sin iva], RELATEDTABLE ( 'yourfacttable' ) )

Then create a rank column.

Rank=RANKX(Articulo,[Sum],,ASC)

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.