cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help Needed on RANKX Function

 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_NameB_Rev S_RevCR% Rank_CR%
Rep_11000001000010% 
Rep_2200000100005% 
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.
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Help Needed on RANKX Function

Hi @MKPBI_2805 ,

 

 

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

 

 

4.JPG

 

 

Regards,
Harsh Nathani

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

View solution in original post

8 REPLIES 8
Highlighted
Highlighted
Frequent Visitor

Re: Help Needed on RANKX Function

Power BI FIle is available here.

 

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

 

RANKX Function help needed.

Highlighted
Super User V
Super User V

Re: Help Needed on RANKX Function

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)

 

Highlighted
Frequent Visitor

Re: Help Needed on RANKX Function

Highlighted
Super User V
Super User V

Re: Help Needed on RANKX Function

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.
 
2.JPG1.jpg
 
 
 

Regards,
Harsh Nathani

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

Highlighted
Frequent Visitor

Re: Help Needed on RANKX Function

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.

Highlighted
Super User V
Super User V

Re: Help Needed on RANKX Function

Hi @MKPBI_2805 ,

 

 

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

 

 

4.JPG

 

 

Regards,
Harsh Nathani

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

View solution in original post

Highlighted
Community Support
Community Support

Re: Help Needed on RANKX Function

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:

1.PNG

See my attached pbix file.

 

Best Regards,

Giotto

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors