Forum Discussion

Romani's avatar
Romani
Icon for Helper II rankHelper II
2 years ago
Solved

TOP Achiever Analysis

I have an issue I couldn't solve because I have negative values in my data, and the DAX below cannot rank it. For example, when ranking the ROS KPI and choosing the top 5, it shows only 4. I don't kn...
  • BeaBF's avatar
    BeaBF
    2 years ago

    Romani  this is another topic. Accept the solution and then we can try to fix the issue.

     

    BBF

Romani's avatar
Romani
Icon for Helper II rankHelper II
2 years ago
Ranking Select = IF(
    HASONEVALUE('Ranking Selection'[Ranking]),
    VALUES('Ranking Selection'[Rank Number]),
    0
)
here is my ranking select 
Ranking  Rank Number
Top 33
Top 55
Top 1010
and this my seperated table for the ranking selection
and this the formula of AVG Ros:
Avg ROS = AVERAGE('My Table'[Return on Sales (ROS)])
BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

Romani ok perfect, and what is your desidered output? can you explain it with datas?

 

Thx,

BBF

  • Romani's avatar
    Romani
    Icon for Helper II rankHelper II
    2 years ago

    I have a problem with ranking negative values in my data using DAX. Here is an example of my data:

    Year Customer Name ROS

    2022Client A-0.38%
    2022Client B8.05%
    2022Client C0.89%
    2022Client D14.53%
    2022Client E-3.55%
    2022Client F-0.69%

    I want to rank the customers based on their ROS for the year 2022. The correct ranking should be:

    1. Client D (14.53%)
    2. Client B (8.05%)
    3. Client C (0.89%)
    4. Client A (-0.38%)
    5. Client F (-0.69%)

    However, my dashboard is only showing 4 customers when I select the top 5. How can I fix this so it shows all 5 customers correctly, including the ones with negative values?


    i Think the problem with my dax especially the below part:

    RANKX(ALL('Credit Benchmarking'[Customer Name]), [Avg ROS], , DESC,Dense)



    • BeaBF's avatar
      BeaBF
      Icon for Super User rankSuper User
      2 years ago

      Romani Ok, here my measures to correctly rank Clients as in your output:

       

      Avg ROS = SELECTEDVALUE('Credit Benchmarking'[ROS.3])
      Customer ROS Rank =
      RANKX(
          ALL('Credit Benchmarking'[Customer Name]),
          [Avg ROS],
          ,
          DESC,
          DENSE
      )
       
      as you can see:

      So, modify the AVG ROS.

       

      BBF

      • Romani's avatar
        Romani
        Icon for Helper II rankHelper II
        2 years ago

        to undestand well i have slicer for top achiever 
        when iam chosing the top 5 as the above picture, the output is that showing me only top 4:

        when iam pasting your DAX 

        Customer ROS Rank =
        RANKX(
            ALL('Credit Benchmarking'[Customer Name]),
            [Avg ROS],
            ,
            DESC,
            DENSE
        ) it deleting the rank selection