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

Issues with RANKX measure when adding additional dimension column

My rank measure works well when only introducing the ranked items, but when I introduce a category for each item (in this example, it's a geographic region) the rank measure returns false ties and skips unused rank numbers.  See screenshot below, where the table on the left omits the geograhic location, and the table on the right includes it.

 

rankx.png

 

 

 

 

 

 

Generically, the rank and ranked measure look like this:

 

% RANK = 

RANKX (
    ALLSELECTED ( 'Dimension'[Item] ),
    CALCULATE ( [% Measure], ALLEXCEPT ( 'Dimension', 'Dimension'[Item] ) )
)

 

% Measure = 

DIVIDE (

SUM ( 'FACT_1'[COLUMN_1] ),
SUM ( 'FACT_2'[COLUMN_2] ),
BLANK()

)

 

Am I missing something to control for the additional category added to the visual?  My understanding is that this should only come into play if I wanted to rank within each category, but this rank measure is independent of the category.

 

NOTE: I'm trying to replicate within a dummy dataset to share but have not been successful yet.  

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Resolved this with the updated measure:

 

% RANK = 

RANKX (
    ALL ( 'Dimension' ),
    CALCULATE ( [% Measure], ALLEXCEPT ( 'Dimension', 'Dimension'[Item], 'Dimension'[Category]) )
)

View solution in original post

1 REPLY 1
Frequent Visitor

Resolved this with the updated measure:

 

% RANK = 

RANKX (
    ALL ( 'Dimension' ),
    CALCULATE ( [% Measure], ALLEXCEPT ( 'Dimension', 'Dimension'[Item], 'Dimension'[Category]) )
)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors