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?

[BeaBF]

@Romani ok, can you share the pbix file or some sample data on which you are calculating this?

Sample data - expected output.

BBF

[Romani]

Ranking Select = IF(

    HASONEVALUE('Ranking Selection'[Ranking]),

    VALUES('Ranking Selection'[Rank Number]),

    0

)
here is my ranking select 
Ranking  Rank Number

Top 3	3
Top 5	5
Top 10	10

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]

@Romani ok perfect, and what is your desidered output? can you explain it with datas?

Thx,

BBF

[Romani]

I have a problem with ranking negative values in my data using DAX. Here is an example 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%

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]

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

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]

@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

[Romani]

romani.adel93@gmail.com

[BeaBF]

Sent! Let me know.

BBF

[BeaBF]

@Romani Hi! What is Ranking Select and Avg Ros? can you paste formulas?