Forum Discussion

TMoney's avatar
TMoney
Frequent Visitor
3 years ago
Solved

Difference between RANKX in Matrix and Card visuals

Hello, I've reached out to two experts on this, and neither has been able to figure this one out.   I have a basic RANKX formula, ranking Sales: Rank Sales = RANKX ( ALL( 'aims teams' ), [SUM....
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi, TMoney 

    I'm sorry for replying so late, I've just seen your message.

    Please checked the attached pbix file. You may need to modify the measure formula like:

    New Rank Basic = 
VAR tab =
    ADDCOLUMNS (
        ALLEXCEPT('aims teams','aims teams'[Index]),
        "SUM.$Sales", [SUM.$Sales],
        "_Rank basic", [Rank basic]
    )

RETURN
    MINX(tab, [_Rank basic] )

     

    Best Regards,
    Community Support Team _ Eason

     

     

TMoney's avatar
TMoney
Frequent Visitor
3 years ago

Yes.  See the solution below.  

Kansetsuwaza's avatar
Kansetsuwaza
Frequent Visitor
3 years ago

Tried to apply your query in my visual but did not work.

Basically, I am trying to do a hotel benchmark analysis in terms of their occupancy rates. 
As you see below, the MATRIX rank and CARD rank are not matching. 
I have 2 slicers that show hotel names and locations. In some cases, I would like to increase my sample size by choosing more than one location (it's A+B locations in my below sample ) but the rank level seems incorrect when I chose multiple locations.

I chose Hotel 2, It's rank should be 2 under location A and 3 under location A+B but CARD shows the rank as 2 for both situations.

Rank Occ = IF(HASONEVALUE('Survey Data'[Hotel Name]),RANKX(ALL('Survey Data'[Hotel Name]),[Occupancy Rate (%)]))
rankx new = RANKX(CROSSJOIN(ALL('Survey Data'[Hotel Name]),ALLSELECTED('Survey Data'[Location])),[Occupancy Rate (%)])