rank report-to hierarchy based on person he report to

[shower999]

Dear all experts, I need some help on a custom solution in PowerBI desktop.

I have a sales and Sales team table.

my Sales team tables looks like

Account	Person	Job title	Manager	Manager's Manager
X	Victor	Sales	Mgr A	Mgr B
Y	Frank	Sales	Mgr B	Director A
Z	John	Sales	Director A	Director B

I can't filter properly by using Manager or Manager's Manager fields

each of these names come with email address. I want to use some DAX code or Query editor to build a proper hierarchy with ranking. so i can filter person by ranking 1 or 2. 

i assume the output I want is like this. (below is the correct expected result)

Account	Rank 5	Rank 4	Rank 3	Rank 2	Rank 1
X	Victor	Mgr A	Mgr B	Director A	Director B
Y		Frank	Mgr B	Director A	Director B
Z		John	Mgr B	Director A	Director B

[v-eachen-msft]

Hi @shower999 ,

According to my understanding, I think that the "rank1" of X and Y should be "Director B",  because "Director B" is the manager of "Director A".  Could you describe the relationship between them more clearly? Maybe I misunderstood some content.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shower999]

@v-eachen-msft 

you are right. i'll rewrite a little of the ranking table in my original post.