Issues with RANKX and TOPN

[Giorgi1989]

Dear All,

What I am trying to achieve is to rank top 10 customers by Gross Margin % (which is a calculated measure). I have two DAX formula as follows:

Customer rank based on GM% =

Calculate(

RANKX(

ALLSELECTED('Customer (Margin)'[Customer]),

[Gross Margin %],,DESC),

FILTER('Customer (Margin)', 'Customer (Margin)'[Gross Margin %] <> BLANK () )

)

Top 10 customers by GM% =

IF(

NOT(ISBLANK([Gross Margin %])),

MAXX(

TOPN(

10, SUMMARIZE('Customer (Margin)','Customer (Margin)'[Customer],'Customer (Margin)'[GM%])),'Customer (Margin)'[Gross Margin %]))

When I throw in these measures in the Matrix, what I get is this:

Any ideas where I am making an error? 

My expected output is:

Customer A - Gross Margin % 65% - Rank 1

Customer B - Gross Margin % 65% - Rank 2

etc.

Any help appreciated.

Thank you!

[tamerj1]

Hi @Giorgi1989 
Can you please advise what are you slicing by in your matrix? Why are you taking the MAXX of the TOPN(10)? As this is just the same as simple MAXX

[Giorgi1989]

Hi, 

Thank you for the quick reply. 

I use 'calendar month' to slice the matrix. 

[tamerj1]

@Giorgi1989 
You may try the following 

Customer rank based on GM% =
RANKX (
    FILTER ( 'Customer (Margin)', 'Customer (Margin)'[Gross Margin %] <> BLANK () ),
    [Gross Margin %],
    ,
    DESC
)

Top 10 customers by GM% =
IF (
    NOT ( ISBLANK ( [Gross Margin %] ) ),
    MAXX (
        ADDCOLUMNS ( 'Customer (Margin)', "@GrossMagin", [Gross Margin %] ),
        [@GrossMagin]
    )
)

[Giorgi1989 · Accepted solution]

Thank you for your suggestions and time. In the end, I decided to have an entirely different approach to creating a page with margin information, top and bottom performers, both customers and products.