TOP Achiever Analysis

[Romani]

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?

[Romani]

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]

@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]

@Romani i've sent you the updated pbix file.

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

BBF

[Romani]

 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]

@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]

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

[BeaBF]

@Romanino, that's enough, it should switch correctly on all filters.

BBF

[Romani]

it is very good till now 
but i have very little issue i want to view the top 3 or 5 or 10
even no year is selected or i select more than one year together

[BeaBF · Accepted solution]

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

BBF

[BeaBF]

@Romani You can try with this new flag measure:

Flag_TOP =

VAR year_selected = IF(HASONEVALUE('Credit Benchmarking'[Year]), "TRUE", "FALSE")

RETURN

IF( (year_selected= "TRUE"  && SELECTEDVALUE('Credit Benchmarking'[ROS Rank_year]) <= 'Ranking Selection'[Ranking Select]) || (year_selected = "FALSE" && SELECTEDVALUE('Credit Benchmarking'[ROS Rank]) <= 'Ranking Selection'[Ranking Select]), "TRUE", "FALSE")

you have to add as tooltip your last calculated column and the first one i sent you:

ROS Rank =

RANKX(

    ALL('Credit Benchmarking'), 'Credit Benchmarking'[ROS.3],, DESC, Dense)

because the last one created take into account year and kpis, this one instead creates a flat rank. 

BBF

[Romani]

To ensure the correct implementation of ranking measures and flags for "Return on Sales (ROS)" in Power BI, I should follow these steps:

1. Create the column for overall ROS ranking:

   ROS Rank = RANKX( ALL('Credit Benchmarking'), 'Credit Benchmarking'[Return on Sales (ROS)], , DESC, DENSE )

2. Create the column for ROS ranking based on year, industry, category, and sub-industry:

   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 )

3. Create the measure to flag the top rankings based on the selected criteria:

   Flag_TOP = VAR year_selected = IF(HASONEVALUE('Credit Benchmarking'[Date]), "TRUE", "FALSE") RETURN IF( (year_selected = "TRUE" && SELECTEDVALUE('Credit Benchmarking'[ROS Rank_year_industry_category_subindustry]) <= 'Ranking Selection'[Ranking Select]) || (year_selected = "FALSE" && SELECTEDVALUE('Credit Benchmarking'[ROS Rank]) <= 'Ranking Selection'[Ranking Select]), "TRUE", "FALSE" )

Despite following these steps, I am not getting the desired output. Specifically, when choosing the rank without considering the year, it does not reflect correctly.

[BeaBF]

@Romani have you put both the calculated column as tooltip in the visual?

[BeaBF]

@RomaniI probably understood, the first formula must be like this:

ROS Rank = 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'[Industry GB Lease Classification] = CurrentIndustry && 'Credit Benchmarking'[Category] = CurrentCategory && 'Credit Benchmarking'[Sub-Industry (level 4)] = CurrentSubIndustry ), 'Credit Benchmarking'[Return on Sales (ROS)], , DESC, DENSE )

Try.

BBF

[Romani]

no it doesnt work 

[BeaBF]

@Romani what it's not working?

BBF

[Romani]

what i want when iam chosing the rank without year to reflect on the visuals
and when iam chosing the industry and rank it reflect too on the visuals 
i dont want my ranking to be reflect if iam chosing one filter or iam choosing more than one

[BeaBF]

@Romani Can you explain with some examples? on the last pbix i 've sent to you, so that we can restart from that point.

BBF

[Romani]

I didnt get any answer from your side

[BeaBF]

@Romani i wrote you if you can explain the problem with some examples on the last pbix i 've sent to you, so that we can restart from that point.

BBF

[Romani]

Please check your email