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

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
Icon for Super User rankSuper 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
    Icon for Super User rankSuper User
    2 years ago

    Romani i've sent you the updated pbix file.

     

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

     

    BBF

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

       I want to create a report where users can use slicers to select between different categories, industries, or sub-industries. Based on their selection, the report should display the top achievers for the chosen category, industry, or sub-industry, for a specific year or across two years. And in the above Pic these are the KPIs

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

        Romani you have to add all the categories in the rank measure, if you want the top three/five/ten for each of them, as i did for the year, so:

        ROS Rank_year_industry =
        VAR CurrentYear = 'Credit Benchmarking'[Year]
        VAR CurrentIndustry = 'Credit Benchmarking'[Industry]
        RETURN
        RANKX(
        FILTER(
        ALL('Credit Benchmarking'),
        'Credit Benchmarking'[Year] = CurrentYear &&
        'Credit Benchmarking'[Industry] = CurrentIndustry
        ),
        'Credit Benchmarking'[ROS.3],
        ,
        DESC,
        Dense
        )

        In this case i added the condition for "Industry". 

         

        Please accept the first answer as solution, these are evolutions of the request.

         

        BBF