Help Needed on RANKX Function

[MKPBI_2805]

 I am using below Formula to get Ranks of Sales Reps.

RANKX(ALLSELECTED('Sheet2'),CALCULATE(DIVIDE(sum('Sheet2'[S_Rev]),sum('Sheet2'[B_Rev]),BLANK())),,DESC,Dense)

My DataSet has Columns - Rep_Name , Role_Type , Product_Type, Quarter , Quarter_To_Day , B_Rev, S_Rev Fields.

Measure : CR%= DIVIDE(sum('Sheet2'[S_Rev]),sum('Sheet2'[B_Rev]),BLANK())

Slicers on Top

Role_Type     ,   Product_Type

Rep_Name	B_Rev	S_Rev	CR%	Rank_CR%
Rep_1	100000	10000	10%
Rep_2	200000	10000	5%

Role_Type , Product_Type are added in the Table , Rankx() is working fine. But when removed from Table and added as Slicers Rankx() is giving wrong results.

[harshnathani]

Hi @MKPBI_2805 ,

Use the following measure:

Rank_CR% = RANKX(ALLSELECTED('Sheet2'),DIVIDE(CALCULATE(sum('Sheet2'[DT Services Revenue])),CALCULATE(sum('Sheet2'[Base_Rev]))),,DESC,Dense)

and 

Dont' Summarize in DT Services Revenue.

Regards,
Harsh Nathani

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

[MKPBI_2805]

Have updated the PBI file with actual columns needed in reporting.

https://drive.google.com/file/d/1AcRE06ckGJbc-T-VxXUQHBU_ctC9Lmip/view?usp=sharing

Could you pls help.

[v-gizhi-msft]

Hi,

Please try this measure:

Rank_CR% =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( Sheet2 ),
        Sheet2[Rep Name],
        Sheet2[AI Score],
        "CR%_CQTD", [CR%_CQTD]
    )
RETURN
    RANKX ( a, CALCULATE ( [CR%_CQTD] ),, DESC, DENSE )

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[harshnathani · Accepted solution]

Hi @MKPBI_2805 ,

RANKX(ALLSELECTED(Sheet2[Rep Name],Sheet2[AI Score]),[CR%_CQTD],,DESC,Dense)

Regards,
Harsh Nathani

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

[amitchandak]

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/3...

[MKPBI_2805]

Power BI FIle is available here.

https://drive.google.com/file/d/1AcRE06ckGJbc-T-VxXUQHBU_ctC9Lmip/view?usp=sharing

RANKX Function help needed.

[harshnathani]

Hi @MKPBI_2805 ,

Cannot access the file.

Have sent a request . Pls share file.

Regards,
Harsh Nathani

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

[MKPBI_2805]

try this link.

https://drive.google.com/file/d/1AcRE06ckGJbc-T-VxXUQHBU_ctC9Lmip/view?usp=sharing