Top N By Each Category

[Anonymous]

Hey guys ,
I have a complex client requirement here ,So i have a data as mentioned below:

Project Name	Business	Job Fee	Expected Fee	Status	LG	Reason
Proj1	Audit	10000		Won	Name1	XYZ
Proj 2	Advisory		10000	Lost	Name 2	XYZ
Proj 3	Risk		10000	Live	Name 3	XYZ

Now the client wants to know what are his top 5 projects in each Business for won,lost and live along with the detail such as LG and reason.According to their logic the won projects should be ranked on the basis of Job fee whereas the lost and live projects should be ranked on the baiss of the Expected fee column.

Right now i am really having a hard time to work on their requirement.Please help me

[Anonymous]

Here's your solution. Please bear in mind that the Product Rank only works for selections made from dimensions other than Product. If you start slicing by GL or Reason, which are in Product, it'll not work the way you'd expect it to. But this is OK because this rank is only to be used for this particular scenario.

https://1drv.ms/u/s!ApyQEauTSLtOgYNAR-eLVQ_xHNrFLA?e=ewmvbd

Best

D

[Anonymous]

Is it ranking on the basis of Expected fee in Lost statusID?

[Anonymous]

It's ranking exactly as you wanted it.

Best
D

[Anonymous]

@Anonymous Thank you for your time and effort really appericiate it but im really having a hard time to understand the file.
Can we communicate in DM or somewhere else ?