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.$CFLV Sales] )
 
Within a Matrix visual, it is performing exactly how you would expect:
However, when I create a Card visual and filter to a particular row, it can sometimes return the wrong result:
But, some rows return the right result:
 
What's going on here?
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

     

     

11 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi, TMoney 

    It might be related to the formula of [SUM.$CFLV Sales].
    Please provide a sample pbix for further research.

     

    Best Regards,
    Community Support Team _ Eason

  • Kansetsuwaza's avatar
    Kansetsuwaza
    Frequent Visitor
    3 years ago

    Have you managed to solve this issue becasue I have the same problem and could not find a solution.

    • 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 (%)])

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi, 

     

    I tried using this formula above but I am getting the rank to show as 1 for everything. This is because it is calculating the minimum value of the rank column which is 1. Any ideas on how to solve this?