Top N by each category

[Anonymous]

Hello Guys,
i need a top n analysis with the folllowing data i have 

Project Name	Status	Fiscal Year	Job Fee	Expected Fee	BU	Reason	Lead Generator
Proj1	Won	2019-2020	1000	2000	Audit	New Pipeline	ABC
Proj2	Lost	2018-2019		3000	Tax	The bid was L1	efg
Proj3	Live	2017-2018		5000	Growth		ABC

Each row represents a new pipleine.So we want to find top 10 projects/pipelines in each BU.
The ranking should be based on Job fee in case where the status is "Won" and in case of status "Live & "Lost" the ranking shoulld be on the basis of expected fee.Also the ranking should be for the currrent fiscal year which is "2019-2020".
The requirement is also that we should see the details such as  Reason and LG for each pipeline.

[technolog · Accepted solution]

Firstly, you want to rank projects based on different criteria depending on their status. For projects with a status of "Won", you want to rank them based on "Job fee". For projects with a status of "Live" or "Lost", you want to rank them based on "Expected fee".

Secondly, you're only interested in projects from the fiscal year "2019-2020".

Lastly, you also want to see the "Reason" and "LG" for each pipeline.

Given these requirements, here's a DAX solution for you:

First, you'll need a calculated column to determine the ranking value based on the status:
Ranking Value =
IF(
'Table'[Status] = "Won", 'Table'[Job fee],
IF(
OR('Table'[Status] = "Live", 'Table'[Status] = "Lost"), 'Table'[Expected fee],
BLANK()
)
)
Now, you'll need a measure to rank the projects:
Top 10 Rank =
RANKX(
FILTER(
'Table',
'Table'[Fiscal Year] = "2019-2020"
),
'Table'[Ranking Value],
,
DESC,
Dense
)
To get the top 10 projects, you can use this measure in a visual and filter it to only show projects where Top 10 Rank is less than or equal to 10.

To see the "Reason" and "LG" for each pipeline, simply add these columns to your visual alongside the project name and the ranking measure.

Now, when you use this in a table or matrix visual in Power BI, you'll see the top 10 projects for the fiscal year "2019-2020" ranked based on the criteria you provided, along with the "Reason" and "LG" for each pipeline. Adjust the table or matrix visual to show the columns you want, and you should be good to go!