Forum Discussion
Romani
Helper II
Helper IITOP 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
Super User
Super UserRomani 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
RomaniHelper II
Helper IIplease find the sequence
first step i will create the below column:
ROS Rank =
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:
then put the measure in the filter pane
and then i will repeat the above steps for the rest of the kpis ???
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")
and then i will repeat the above steps for the rest of the kpis ???