DAX - Calculate rank based on Sales metrics for Sales rep

[melvin9900]

Hello Community ,

    I am new to PowerBI/Dax world.  We have a report which displays Sales reps performance details. 

It has a dropdown with Sales reps name and once selected it displays the details of corresponding Sales rep.

Something like below, simplified version just to explain.

Sales Rep Name : AAAA

Metric         Amt     Rank 

Revenue     1000     Calculate Rank based on Revenue amt (desc) across all reps: Expected value from the table below :  2

Margin       10         Calculate Rank based on Margin amt (desc) across all reps: Expected value from the table below :  3

Debt           2000      Calculate Rank based on Budget amt (ascending) across all reps: Expected value from the table below :  2

So we have to calculate the rank of the SaleRep for each Metric across all the reps  based on the Amount.

The table structure is 

I am trying to calculate the rank using the DAX formula like Rankx but not getting the value expected. 

Can someone please help me here.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I created for both calculated column and calculated measure.

Ranking CC = 
VAR currentmetrics = Data[Metric]
VAR newtable =
    FILTER ( Data, Data[Metric] = currentmetrics )
VAR rankingcurrentmetric =
    RANKX ( newtable, Data[Amount],, DESC )
VAR rankingdebtmetric =
    RANKX ( newtable, Data[Amount],, ASC )
RETURN
    SWITCH (
        TRUE (),
        Data[Metric] IN { "Revenue", "Margin" }, rankingcurrentmetric,
        rankingdebtmetric
    )

Amount measure: = 
IF ( ISFILTERED ( Metrics[Metric] ), SUM ( Data[Amount] ) )

Ranking measure: = 
VAR currentmetrics =
    MAX ( Metrics[Metric] )
VAR newtable =
    FILTER ( ALL ( Data ), Data[Metric] = currentmetrics )
VAR rankingcurrentmetric =
    RANKX ( newtable, Data[Amount measure:],, DESC )
VAR rankingdebtmetric =
    RANKX ( newtable, Data[Amount measure:],, ASC )
RETURN
    IF (
        ISFILTERED ( Metrics[Metric] ),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Metrics[Metric] ) IN { "Revenue", "Margin" }, rankingcurrentmetric,
            rankingdebtmetric
        )
    )