Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Duplicated Rank value

Hi everyone,

 

Im getting the following problem:

 

The rank = RANKX( ALLSELECTED( DIM_INVOICE_SUPPLIER[NORMALIZED_NAME] ),[Spend Selection CY],,DESC)

As you can see there are some duplicated Ranks, this should be normal if I have duplicated Spend but its not the case , the spend is diferent but the rank is duplicated. 

 

What I see is if I remove the % field the rank work diferentry even if the spend is the same:

 

Someone have any idea?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Try

    The rank =
RANKX (
    FILTER (
        ALLSELECTED ( DIM_INVOICE_SUPPLIER ),
        [Suppliers] = MAX ( 'DIM_INVOICE_SUPPLIER'[Suppliers] )
    ),
    [Spend Selection CY],
    ,
    DESC
)

    If you still have problems, please provide some sample data and expected results. If it is a .pbix file is the best.

     

     

     

    Best Regards,

    Stephen Tao

     

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , is this column -NORMALIZED_NAME - same as the suppliers in visual ?

     

    if not try like

     

    The rank = RANKX( ALLSELECTED( DIM_INVOICE_SUPPLIER[suppliers] ),[Spend Selection CY],,DESC)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amitchandak,

       

      Thanks for the answer.

       

      Yes is the same field NORMALIZED_NAME, sorry for not indicating it in the post.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Still not working, the result of that function is even more duplicated ranks

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        Try

        The rank =
RANKX (
    FILTER (
        ALLSELECTED ( DIM_INVOICE_SUPPLIER ),
        [Suppliers] = MAX ( 'DIM_INVOICE_SUPPLIER'[Suppliers] )
    ),
    [Spend Selection CY],
    ,
    DESC
)

        If you still have problems, please provide some sample data and expected results. If it is a .pbix file is the best.

         

         

         

        Best Regards,

        Stephen Tao

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Edit:

     

    The other measure: Class is this:



    Class =
    // 1 – defines percentages
    VAR topPercentage = 0.8
    VAR middlePercentage = 0.95
    VAR totalMargin = CALCULATE([Spend Selection CY], ALLSELECTED())

    // 2 – finds the ranks separating top class from middle class, and middle class from bottom class
    VAR topRankNumber = MAXX(FILTER(ALLSELECTED(DIM_INVOICE_SUPPLIER[NORMALIZED_NAME]), [Cumulated margin]<= totalMargin* topPercentage), [Rank] )
    VAR middleRankNumber = MAXX(FILTER(ALLSELECTED(DIM_INVOICE_SUPPLIER[NORMALIZED_NAME]), [Cumulated margin]<= totalMargin* middlePercentage), [Rank] )

    // 3 – returns the class of the selected product

    RETURN
    IF(
    OR(ISBLANK([Rank]), ISBLANK([Spend Selection CY])), BLANK(),
    IF( [Rank]<=topRankNumber,"Top",IF([Rank]<=middleRankNumber,"Middle","Bottom")))


    I took it from google and adapted it