Rankx on a matrix

[Anonymous]

i am trying to do rankx on a matrix table but when i drill down i would like the rankx to recalculate when i drill down, currently i have the following fields in my matrix and want to do the rank by total sales. Any help would be amazing;

[v-xicai]

Hi @Anonymous ,

You may create measure like DAX below .

Endleaf Rank =
SWITCH (
    TRUE (),
    ISINSCOPE ( Sales[Username] ), RANKX (
        ALLSELECTED ( Sales[Username] ),
        CALCULATE ( SUM ( Sales[Point] ) ),
        ,
        DESC,
        DENSE
    ),
    ISINSCOPE ( Sales[Date].[Month] ), RANKX (
        ALLSELECTED ( Saless[Date].[Month] ),
        CALCULATE ( SUM ( Sales[Point] ) ),
        ,
        DESC,
        DENSE
    ),
    ISINSCOPE ( Sales[store]]] ), RANKX (
        ALLSELECTED ( Store[store] ),
        CALCULATE ( SUM ( Sales[Point] ) ),
        ,
        DESC,
        DENSE
    ),
    ISINSCOPE ( Sales[Product]]] ), RANKX (
        ALLSELECTED ( Store[Product] ),
        CALCULATE ( SUM ( Sales[Point] ) ),
        ,
        DESC,
        DENSE
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

thanks amy, still not geting the outcome i would expect as i am still getting just loads of 1 when i drill down;

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Greg_Deckler]

@Anonymous - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

[harshnathani]

Hi @Anonymous ,

Please share sample data and expected output.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani currently if i am just looking at the table before i drill down from user the ranking is current using;

Rankx(all(username),[Total Sales])

but when i drill down to month it just gives me 1 on everything then when i further drill down to the other options it is always 1 when i would expect it to recalculate and rank the months then rank the next category etc

[harshnathani]

Hi @Anonymous ,

Since it is matrix and you need to drill down,  you will need to use ISINSCOPE.

See this article

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Regards,

Harsh Nathani

[Anonymous]

@harshnathani i have tried doing ISINSCOPE and dont get any different result it is still just giving me a 1 for everything when i drill down;

Endleaf Rank =

SWITCH(

TRUE(),

ISINSCOPE(Sales[Username]), RANKX(ALLSELECTED(Sales[Username]),[Total Points], ,DESC,Dense),

ISINSCOPE(Sales[Date].[Month]),RANKX(ALLSELECTED(Saless[Date].[Month]), [Total Points], ,DESC, Dense),

ISINSCOPE(Sales[store]]]), RANKX(ALLSELECTED(Store[store]),

[TotalSales], ,

DESC,

Dense)

)