topic Re: TOP Achiever Analysis in DAX Commands and Tips

TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 08:33:35 GMT

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?

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 09:03:48 GMT

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

Re: TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 09:31:00 GMT

Ranking Select = IF(

HASONEVALUE('Ranking Selection'[Ranking]),

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

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

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 09:36:00 GMT

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

Thx,

BBF

Re: TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 09:45:47 GMT

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:

Client D (14.53%)
Client B (8.05%)
Client C (0.89%)
Client A (-0.38%)
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)

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 09:57:37 GMT

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

Re: TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 10:21:11 GMT

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

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 10:36:09 GMT

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

Re: TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 10:43:21 GMT

romani.adel93@gmail.com

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 10:47:42 GMT

Sent! Let me know.

BBF

Re: TOP Achiever Analysis

Romani — Mon, 15 Jul 2024 11:17:44 GMT

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.

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 12:06:33 GMT

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

Re: TOP Achiever Analysis

BeaBF — Mon, 15 Jul 2024 12:24:55 GMT

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