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

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
      • BeaBF's avatar
        BeaBF
        Icon for Super User rankSuper User
        2 years ago

        Romani Ok, Clear. So we need to modify the logica.

        Instead of a measure, calculate this new column and put it as tooltip on your visual:

        ROS Rank =
        RANKX(
            ALL('Credit Benchmarking'), 'Credit Benchmarking'[ROS.3],, DESC, Dense)
        then create this measure:
        Flag_TOP = IF(SELECTEDVALUE('Credit Benchmarking'[ROS Rank]) <= 'Ranking Selection'[Ranking Select], "TRUE", "FALSE")
        Apply this Flag in the filter pane of your visual and set it to "TRUE".

         

         
        It will work:

         

         If you send me your email, i can send you my pbix example file.

         

        BBF