Rank of Selected + & - 1 pos

[Anonymous]

Hello, 

Task: get subject (Manager name from selection) and create table (measure) with 3 managers names, report should look like:

rows:

• One row above manager (by rank of result)
• Selected manager
• One row below manager (by rank of result)

columns:

• rank
• plan
• fact
• result

I've found this solution and it was very, very helpfull! @TomMartens - thank you very much!

It helped me to create this report:

 

But when I want to add rank, something goes wrong and I get "third" table.

The code:

Rank of Selected + & - 1 pos. =

var __subjectsAboveAndBelow =    

UNION(

FILTER(

                FIRSTNONBLANK(Sales_Fact[Manager_Name];1)

                ;Sales_Fact[Manager_Name] = [Selected Manager]

                )

;

UNION(

SUMMARIZE(

        FILTER(           

            ADDCOLUMNS(

                GROUPBY(

                    Sales_Fact

                    ;Sales_Fact[Manager_Name]

                )

                ;"theRank"; [Rank]

            )

            ;[theRank] > [RANK of selected subject] && [theRank] <= [RANK of selected subject] + 1

        )

;Sales_Fact[Manager_Name]

)

;SUMMARIZE(

        FILTER(           

            ADDCOLUMNS(

                GROUPBY(

                    Sales_Fact

                    ;Sales_Fact[Manager_Name]

                )

                ;"theRank"; [Rank]

            )

            ;[theRank] < [RANK of selected subject] && [theRank] >= [RANK of selected subject] - 1

        )

;Sales_Fact[Manager_Name]

)

)

)

return

CALCULATE(

          RANKX(

              ALL(Sales_Fact[Manager_Name])

              ;[Result]

              ;

              ;DESC

              ;Dense

          )

          ;__subjectsAboveAndBelow

)  

How to get only three rows (as in "second table" (center)) but with columns:

• rank,
• plan,
• fact,
• result

Thanks in advance

Sales_smart_ranking.pbix

[Anonymous · Accepted solution]

Problem solved, thanks for attention