Forum Discussion

villa1980's avatar
villa1980
Icon for Resolver II rankResolver II
2 years ago

Incorrect Rank on %

Hi all,    I have a Field [METRICS] which calculates a percentage from another field [CONT] (Contains Sales, MArgin, Budget by POS Name) which is joined to field [CSD] that contains POS Names (Eg. ...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

villa1980 , Try using DAX
Rank Calc =
RANKX(
ALL('CSD'[POS Name]),
[MtTrgt_%],
,
DESC,
DENSE
)

  • villa1980's avatar
    villa1980
    Icon for Resolver II rankResolver II
    2 years ago

    My apologies that seems to work brilliantly thank-you, I have another issue now, if I bring in POS No into the table the Ranking reverts back to one.
    How do I overcome this issue?

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi villa1980 ,

       

      Try below expression:

      Measure = var _t = ADDCOLUMNS('CSD',"RANK",RANKX(FILTER(ALL('CSD'),[POS NAME]=EARLIER([POS NAME])),[MtTrgt_%],,DESC,Dense))
RETURN MAXX(_t,[Rank])

       

      Hope it helps!

       

      Best regards,
      Community Support Team_ Scott Chang

       

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

      • villa1980's avatar
        villa1980
        Icon for Resolver II rankResolver II
        2 years ago

        Thank-you for your reply, that has worked very well thank-you.

        This should be an easy one, I have managed to produce a rank calculation based on a hirarchy which is below. However it is ranking blanks, I know I need Not (ISBLANK(....but am unsure where I put this within this calculation.

        Any Help would be appreciated.


        Rank by NPS =
            VAR IsRMFiltered = ISFILTERED('CSD Master'[Regional Manager])
            VAR IsAOMFiltered = ISFILTERED('CSD Master'[New AOM 1 May 2024])
            VAR IsPOSFiltered = ISFILTERED('CSD Master'[POS Name])
        RETURN
                IF (NOT IsRMFiltered &&  NOT IsAOMFiltered && IsPOSFiltered,
                RANK(DENSE,ALL('CSD Master'[POS Name]), ORDERBY(CALCULATE(SUM(NPS[NPS Score])),desc)),
                IF (NOT IsRMFiltered &&  IsAOMFiltered && IsPOSFiltered,
                RANK(DENSE,ALL('CSD Master'[New AOM 1 May 2024]), ORDERBY(CALCULATE(SUM(NPS[NPS Score])),desc)),
                IF (IsRMFiltered &&  Not IsAOMFiltered && IsPOSFiltered,
                RANK(DENSE,ALL('CSD Master'[Regional Manager]), ORDERBY(CALCULATE(SUM(NPS[NPS Score])),desc)),
                BLANK()
        )))