How to rank a list by 2 values or criteria with rankx (DAX expression)

[mnll7]

Hi Everyone,

I am new to Power BI and using DAX. I need help with creating the appropriate Rankx expression for the following scenario:

Issues need to be ranked by two criteria: count (number of times issues have occured) and severity (S2 = highest rating and S0 = lowest rating).

Essentially, the issues column should be ranked by the following equation Count * Severity with severity rating weight as S2 = 4, S1 = 3, S0 = 2. The result should look as shown below:

Could someone please help me with writing the appropriate Rankx expression to obtain the result described above? I have made several attempts but still keep getting different results. Thank you!

[mnll7]

All,

Thank you for your support and answers.

[Phil_Seamark]

This calculated column might be close

RAnk = 
VAR Sev = INT( REPLACE('Table2'[Severity],1,1,""))
VAR Sev2 = SWITCH(
                TRUE() , 
                Sev=2,4,
                Sev=1,3,
                --ELSE--
                2)
RETURN     
        RANKX(ALL('Table2') , [Count] * Sev2)

[ccakjcrx · Accepted solution]

Hey @mnll7!

I was able to get the ranking without creating another table; however, you have to create two measures: one to substitute the severity values with the numbers you indicated (e.g., 4, 3, 2) and the other for ranking.

Here is the Switch Measure:

MsrSevSwitch = 
IF(
    HASONEVALUE(Sheet1[Severity]),
    SWITCH(VALUES(Sheet1[Severity]),
        "S2",4,
        "S1",3,
        "S0",2
    ),""
)

Here is the RANK Measure:

MsrRank = 
RANKX(
    ALL(Sheet1),
    CALCULATE(
        SUMX(
            Sheet1,
            [MsrSevSwitch] * Sheet1[Count ]
        )
    )
    ,,ASC,Dense
)

Click HERE for my .pbix file.

Here is a screenshot of the table:

[Interkoubess]

Hi @mnll7,

I created a second table with the severity rate ( called Rate) , link it to the principal table then I  created a column for Count * Severity here we go:

Ranks= RANKX(ALL('Sample'),'Sample'[Count]*RELATED(Test[Coef]))

Ninter

[mnll7]

I am new to Power BI and the use of DAX expressions. I have attempted to apply the appropriate Rankx expression for the following data shown below to rank the issues by count and severity only.

I would like to rank the issues by number of times it has occured (count) and by severity (S2 = highest rating and S0=lowest rating). Essentially, I am ranking by Count * Severity with the severity weight as S2 = 4, S1 = 3, S0 = 2. The end result should look as shown below:

Could someone please help me with writing the appropriate Rankx DAX expression to obtain the result described above? I have tried several rankx equations using measures and other functions within the rankx expression but no luck.

Thank you!

[ccakjcrx]

Hey @mnll7!

This is a duplicate post, and the other one has solutions posted to it. Can you please delete this one?