Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MKPBI_2805
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

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
harshnathani
Community Champion
Community Champion

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)

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.

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

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)

Power BI FIle is available here.

 

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

 

RANKX Function help needed.

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)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.