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, 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

      • Romani's avatar
        Romani
        Icon for Helper II rankHelper II
        2 years ago
        please find the sequence 
        first step i will create the below column:
        ROS Rank =
        RANKX(
            ALL('Credit Benchmarking'), 'Credit Benchmarking'[Return on Sales (ROS)],, DESC, Dense)
        then the below column:
        ROS Rank_year_industry_category_subindustry =
        VAR CurrentYear = 'Credit Benchmarking'[Date]
        VAR CurrentIndustry = 'Credit Benchmarking'[Industry GB Lease Classification]
        VAR CurrentCategory = 'Credit Benchmarking'[Category]
        VAR CurrentSubIndustry = 'Credit Benchmarking'[Sub-Industry (level 4)]
        RETURN
        RANKX(
        FILTER(
        ALL('Credit Benchmarking'),
        'Credit Benchmarking'[Date] = CurrentYear &&
        'Credit Benchmarking'[Industry GB Lease Classification] = CurrentIndustry &&
        'Credit Benchmarking'[Category] = CurrentCategory &&
        'Credit Benchmarking'[Sub-Industry (level 4)] = CurrentSubIndustry
        ),
        'Credit Benchmarking'[Return on Sales (ROS)],
        ,
        DESC,
        DENSE
        )
        then last step to create this dax:
        Flag_TOP = IF(SELECTEDVALUE('Credit Benchmarking'[ROS Rank_year_industry_category_subindustry]) <= 'Ranking Selection'[Ranking Select], "TRUE", "FALSE")
        then put the measure in the filter pane 
        and then i will repeat the above steps for the rest of the kpis ???