Incorrect Rank on %

[villa1980]

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. Centre1, Centre2).

 There are other fields which contain different data and [METRICS] field creates calculations from those.

The % Calc in the metric is MtTrgt_% = sum(CONT[Margin]) / sum(CONT[Margin Budget])
What I would like to do is rank this %, however, when I rank on this metric it just shows 1 not a full rank, what I am doing wrong?

Rank Calc = RANK(DENSE,ALLSELECTED('CSD'[POS Name]), ORDERBY (MtTrgt_%],desc) )

Many Thanks

Alex

[bhanu_gautam]

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

[villa1980]

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]

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]

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()

)))