Forum Discussion

_Loic's avatar
_Loic
New Member
2 years ago
Solved

Formula problem Rank

Hi,

I try this Measure

_Cumul_III =
    Var Mon_Numero = RANKX(ALLSELECTED(Produits[PRO_CODE]),[_Marges_2])
    Var Best_Products = TOPN(Mon_Numero,'Produits',[_Marges_2])
    Var My_Sum = CALCULATE([_Marges_2],Best_Products)
    RETURN My_Sum
 I want to sum the [_Marges_2] for the ranking :
for example If the rank is 2 : 16 248 988.14 + 14 230 309.54
but my measure give only : 14 230 310
Can you explain to me the reason ?
thanks a lot

 

  • BeaBF's avatar
    BeaBF
    2 years ago

    _Loic Let's try a different approach:
    _Cumul_III =
    VAR CurrentProduct = SELECTEDVALUE(Produits[PRO_CODE])
    VAR Mon_Numero = RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense)
    VAR My_Sum =
    CALCULATE(
    SUM([_Marges_2]),
    FILTER(
    ALL(Produits[PRO_CODE]),
    RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense) <= Mon_Numero &&
    SELECTEDVALUE(Produits[PRO_CODE]) = CurrentProduct
    )
    )
    RETURN My_Sum

    BBF

5 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    _Loic  Hi!
    Try with:

    _Cumul_III =
    VAR Mon_Numero = RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense)
    VAR SelectedProducts = TOPN(Mon_Numero, VALUES(Produits[PRO_CODE]), [_Marges_2], DESC)
    VAR My_Sum = CALCULATE(SUM([_Marges_2]), FILTER(ALL(Produits[PRO_CODE]), RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense) <= Mon_Numero))
    RETURN My_Sum

    BBF

    • _Loic's avatar
      _Loic
      New Member
      2 years ago

      Thanks a lot,

      Your code is interesting, better than me.

      But i have the same result ...

      _Cumul_IV =
      VAR Mon_Numero = RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense)
      VAR SelectedProducts = TOPN(Mon_Numero, VALUES(Produits[PRO_CODE]), [_Marges_2], DESC)
      VAR My_Sum = CALCULATE([_Marges_2], FILTER(ALL(Produits[PRO_CODE]), RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense) <= Mon_Numero))
      RETURN My_Sum

       

      • BeaBF's avatar
        BeaBF
        Super User
        2 years ago

        _Loic Let's try a different approach:
        _Cumul_III =
        VAR CurrentProduct = SELECTEDVALUE(Produits[PRO_CODE])
        VAR Mon_Numero = RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense)
        VAR My_Sum =
        CALCULATE(
        SUM([_Marges_2]),
        FILTER(
        ALL(Produits[PRO_CODE]),
        RANKX(ALLSELECTED(Produits[PRO_CODE]), [_Marges_2], , DESC, Dense) <= Mon_Numero &&
        SELECTEDVALUE(Produits[PRO_CODE]) = CurrentProduct
        )
        )
        RETURN My_Sum

        BBF