RANKX results affected by other columns

[ValentinaM]

Hello all! 

I am struggling with RANKX() function right know, so I wanted to ask for help. 

Customer wants to have a table, where he can dynamically choose which dimension will be ranked by which measure. To achieve this, I created 2 parameters, Ranking Dimension (list of dimension which can be ranked) and Ranking Measure (list of measures that can be used to rank the dimension) and I put them in slicers, both single select. Then I created the Rank Top Measure, which looks like this:

Rank Top =
var SelectedDimension = SELECTEDVALUE('Ranking Dimension'[Ranking Dimension Order])
var Ranking =
SWITCH(
    SelectedDimension,
    0, RANKX(FILTER(ALLSELECTED(DIM__CUSTOMER[NAME]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    1, RANKX(FILTER(ALLSELECTED(DIM__CUSTOMER[SOLD_TO_CUSTOMER_ID]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    2, RANKX(FILTER(ALLSELECTED(DIM__CUSTOMER[CUSTOMER_TYPE]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    3, RANKX(FILTER(ALLSELECTED(DIM__CUSTOMER[CUSTOMER_TYPE_GLOBAL]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    4, RANKX(FILTER(ALLSELECTED(DIM__END_CUSTOMER[END_CUSTOMER]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    5, RANKX(FILTER(ALLSELECTED(DIM__END_CUSTOMER[END_CUSTOMER_COUNTRY]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    6, RANKX(FILTER(ALLSELECTED(DIM__CUSTOMER[IFA_NR]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    7, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[MLFB_CODE_MASTER]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    8, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[PCK_MASTER]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    9, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[PRODUCT_DISCOUNT_CLASS_HQ]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    10, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[PRODUCT_DISCOUNT_CLASS_REG]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    11, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[SAP_MATERIAL_ID]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC),
    12, RANKX(FILTER(ALLSELECTED(DIM__MATERIAL[GCK_MASTER]),[SelectedMeasure] <> BLANK()),[SelectedMeasure],,DESC)
)
return
IF(AND(Ranking <= 'TopN'[TopN Value], [SelectedMeasure] <> BLANK()), Ranking, BLANK())

This is working perfectly, when I have in the table only the Selected Dimension and Selected Measure and the Rank. However, customer would like an option to add other columns to see detailed information about chosen dimension or a lower level. But this should be added only to the TOP N results as detailed information and should not affect the RANKX() and change the results. And this is where I am stuck. When I add some other column, the RANKX() results are different. I tried adjusting the formula with ALLEXCEPT() / REMOVEFILTERS(), I created second table which I want to filter based on rankx results, but I cant get it to work. So any help and ideas would be much appreciated 🙏 
To better understand, what I am trying to achieve, here is the example: Green table I have and is working fine, but I want the yellow:

Thank youu!

[Anonymous · Accepted solution]

Hi @ValentinaM ,

Please try this DAX to create a new measure:

RANK = 
VAR CustomerRank =
RANKX(
    ALLSELECTED('Table'),
    CALCULATE(
        SUM('Table'[Sales]),
        ALLEXCEPT('Table', 'Table'[Customer])), ,DESC,Dense)
RETURN
IF(
    CustomerRank <= 5,
    CustomerRank,
    "Null"
)

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