Forum Discussion

Romani's avatar
Romani
Helper 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 know why. Here is a sample of my data:

Year | Customer Name | ROS
2022 | Client A | -0.38%
2022 | Client B | 8.05%
2022 | Client C | 0.89%
2022 | Client D | 14.53%
2022 | Client E | -3.55%
2022 | Client F | -0.69%

Top Avg ROS by Rank =
VAR RankingDimension = VALUES('Credit Benchmarking'[Customer Name])
VAR RankingSelect = [Ranking Select]
RETURN CALCULATE( [Avg ROS], FILTER( RankingDimension, RANKX(ALL('Credit Benchmarking'[Customer Name]), [Avg ROS], , DESC,Dense) <= RankingSelect ))

Can you help me resolve this issue?

  • 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

30 Replies

  • Romani's avatar
    Romani
    Helper 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
      Super 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
        Helper 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)



  • Romani's avatar
    Romani
    Helper II
    2 years ago

    Well received.

    However, the ranking slicer is not affecting the visual as expected. The client has multiple ROS values spanning two or three years, and I also have a slicer for the year.


     

    When I apply your solution, it displays all the clients regardless of selecting the top 3, 5, or 10. Additionally, when I choose a specific year, the output disappears.

    • BeaBF's avatar
      BeaBF
      Super User
      2 years ago

      Romani ok, so modify your calculated column as:

      ROS Rank_year =
      VAR CurrentYear = 'Credit Benchmarking'[Year]
      RETURN
      RANKX(
          FILTER(
              ALL('Credit Benchmarking'),
              'Credit Benchmarking'[Year] = CurrentYear
          ),
          'Credit Benchmarking'[ROS.3],
          ,
          DESC,
          Dense
      )
      than substitute this in the tooltip and also in the Flag_TOP measure.
       
      BBF
      • BeaBF's avatar
        BeaBF
        Super User
        2 years ago

        Romani i've sent you the updated pbix file.

         

        If it's ok, please accept my answer as solution.

         

        BBF