Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

RankX is not Ranking correctly

The other solutions on this forum don't seem to work for some reason.  I must have a tricky one or something.

 

I have a table that has PointsForRank, as you can see that I want to rank over by each OperatorName.  But for some reason rankx is not ranking correctly at all, and I'm at a loss as to why.

 

 

DAX: 

Rank =
RANKX(
    ALLSELECTED('Charge Off Collections'),
    CALCULATE(
    [PointsForRank],
    ALLEXCEPT('Charge Off Collections','Charge Off Collections'[Operator Name], 'Charge Off Collections'[Team Name])
    ),
    ,
    desc,
    Dense)
Need Help
  • mickey64's avatar
    mickey64
    1 year ago

    For your reference.

     

    Step 0: I use these DATA.

     

    Step 1: I correct the formula.

    Rank =
    RANKX(
    ALLSELECTED('Charge Off Collections'),
    CALCULATE(
    SUM('Charge Off Collections'[PointsForRank]),
    ALLEXCEPT('Charge Off Collections','Charge Off Collections'[Operator Name], 'Charge Off Collections'[Team Name])
    ),
    ,
    desc,
    Dense)

     

    Step 2: I make a ’Table’.

     

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    Anonymous What's your PointsForRank formula? Also, try using RANK instead of RANKX

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I tried using the rank function but it was even worse than the rankx

      I'm just confused why the pointsforrank are so clearly expressed in the table, yet the rankx seems to not even be looks at the values at all.  If I understood how the rankx function was actually calculating the rank, I feel like this would be a lot easier.

      here's my pointsforrank dax:

       

      PointsForRank =
      VAR PercentileOfCures = DIVIDE(SUM('Charge Off Collections'[Cured]), CALCULATE(SUM('Charge Off Collections'[Cured]),ALLSELECTED('Charge Off Collections')))
      var cured = (PercentileOfCures * 0.3)
      VAR PercentileOfCollected = DIVIDE([Collected Amount], CALCULATE([Collected Amount],ALLSELECTED('Charge Off Collections')))
      var collected = (PercentileOfCollected * 0.25)
      VAR PercentileOfptpCreated = DIVIDE([PTP Created Count], CALCULATE([PTP Created Count],ALLSELECTED('Charge Off Collections')))
      var ptpCreated = (PercentileOfptpCreated * 0.1)
      VAR PercentileOfptpkept = DIVIDE([PTP Kept Count], CALCULATE([PTP Kept Count],ALLSELECTED('Charge Off Collections')))
      var ptpkept = (PercentileOfptpkept * 0.2)
      VAR PercentileOfdc = DIVIDE([Debit Card Payments], CALCULATE([Debit Card Payments],ALLSELECTED('Charge Off Collections')))
      var dc = (PercentileOfdc * 0.15)
      RETURN (ptpCreated+collected+cured+ptpkept+dc)*100000
  • mickey64's avatar
    mickey64
    Super User
    1 year ago

    For your reference.

     

    Step 0: I use these DATA.

     

    Step 1: I correct the formula.

    Rank =
    RANKX(
    ALLSELECTED('Charge Off Collections'),
    CALCULATE(
    SUM('Charge Off Collections'[PointsForRank]),
    ALLEXCEPT('Charge Off Collections','Charge Off Collections'[Operator Name], 'Charge Off Collections'[Team Name])
    ),
    ,
    desc,
    Dense)

     

    Step 2: I make a ’Table’.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      So I did create a PointsforRank column in the dataset, and the rank works with that.  PBI doesn't like using measures in the calculate field in rankx for some reason.  Tyvm!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I was thinking aoubt making a table specifically for ranking, but why wouldn't the rank work dynamically?  Also the Points for rank is a measure not a column.  I'll post the dax for it below:

       

      PointsForRank =
      VAR PercentileOfCures = DIVIDE(SUM('Charge Off Collections'[Cured]), CALCULATE(SUM('Charge Off Collections'[Cured]),ALLSELECTED('Charge Off Collections')))
      var cured = (PercentileOfCures * 0.3)
      VAR PercentileOfCollected = DIVIDE([Collected Amount], CALCULATE([Collected Amount],ALLSELECTED('Charge Off Collections')))
      var collected = (PercentileOfCollected * 0.25)
      VAR PercentileOfptpCreated = DIVIDE([PTP Created Count], CALCULATE([PTP Created Count],ALLSELECTED('Charge Off Collections')))
      var ptpCreated = (PercentileOfptpCreated * 0.1)
      VAR PercentileOfptpkept = DIVIDE([PTP Kept Count], CALCULATE([PTP Kept Count],ALLSELECTED('Charge Off Collections')))
      var ptpkept = (PercentileOfptpkept * 0.2)
      VAR PercentileOfdc = DIVIDE([Debit Card Payments], CALCULATE([Debit Card Payments],ALLSELECTED('Charge Off Collections')))
      var dc = (PercentileOfdc * 0.15)
      RETURN (ptpCreated+collected+cured+ptpkept+dc)*100000
      • mickey64's avatar
        mickey64
        Super User
        1 year ago

        The table name "Charge Off Collections" appears several times in the formula, but I think an error may be occurring in the calculation because [PointsForRank] is a measure and not a column.


        Let's ask CST to tell us the correct answer! (^^)/

         

        DAX: 

        Rank =
        RANKX(
            ALLSELECTED('Charge Off Collections'),
            CALCULATE(
            [PointsForRank],
            ALLEXCEPT('Charge Off Collections','Charge Off Collections'[Operator Name], 'Charge Off Collections'[Team Name])
            ),
            ,
            desc,
            Dense)