Forum Discussion
Helper IITOP Achiever Analysis
Helper III 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
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
- Romaniplease 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:then put the measure in the filter paneFlag_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 ???