Ranking in columns

[Truelearner]

Hi All 

I need ranks in columns ,below is the sample data and last three columns are the required ranks 

1. Rank of employee sales compared to all employees in the firm

2.Rank of employee sales compared to all employees in his department 

3. Rank of employee Department sales compared to all departments in the firm

NOTE : I want this ranks as columns not as measures 

Dept	Emp	Highlevel	lowlevel	Sales	Emp Firm Rank	Emp Rank in Dept	Emp's Dept Rank
A	1	1	1	10	6	3	2
B	2	1	1	20	1	1	1
C	3	1	1	30	7	2	3
A	4	0	1	40	2	1	2
A	5	1	1	50	3	2	2
B	6	0	1	60	4	2	1
C	7	0	1	70	6	1	3
A	1	1	1	40	6	3	2
A	1	1	1	30	6	3	2
C	3	1	1	50	7	2	3
C	3	1	1	60	7	2	3

@v-chuncz-msft @amitchandak @v-wxu  @mgwena @cham @amitchandak @Greg_Deckler @Mariusz @yij @yij @ v-yingj@v-diye-msft@v-xicai @v-eachen-msft @v-lionel-msft  @v-juanli-msft @v-jayw-msf @v-kelly-msft  @v-zhenbw-msft @harshnathani

[harshnathani · Accepted solution]

Hi @Truelearner ,

Create Calculated Columns.

Venatas Employee Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Emp]))

Rango de la firma Emp1 = RANKX(ALL('Table'[Emp],'Table'[Venatas Employee Wise]),'Table'[Venatas Employee Wise],,ASC)

Venatas Department Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Departamento]))

Rango de Emp en el Departamento1 = RANKX(FILTER('Table','Table'[Departamento] = EARLIER('Table'[Departamento])),'Table'[Venatas Employee Wise],,ASC)

Rango de Departamento de Emp1 = RANKX(ALL('Table'[Departamento],'Table'[Venatas Department Wise]),'Table'[Venatas Department Wise],,ASC)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[v-xuding-msft]

Hi @Truelearner ,

Are the answers helpful? If you make sense, please accept as solution. More people will benefit from this thread.

[mahoneypat · Accepted solution]

Since I had already started on these before seeing the post from @harshnathani , I will post these alternate solutions.  The difference is that these calculate it directly w/o an intermediate column.  Potentially a good example of using the Value term in RANKX on a virtual table (I learned something new on this one).

Emp Firm Rank New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( Ranks, Ranks[Emp] ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Emp] = EARLIER ( Ranks[Emp] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Emp] = EARLIER ( Ranks[Emp] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

Emp Rank in Dept New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Ranks, Ranks[Dept] = EARLIER ( Ranks[Dept] ) ),
            Ranks[Emp]
        ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Emp] = EARLIER ( Ranks[Emp] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Emp] = EARLIER ( Ranks[Emp] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

Emp Dept Rank New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( Ranks, Ranks[Dept] ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Dept] = EARLIER ( Ranks[Dept] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Dept] = EARLIER ( Ranks[Dept] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[harshnathani · Accepted solution]

Hi @Truelearner ,

Create Calculated Columns.

Venatas Employee Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Emp]))

Rango de la firma Emp1 = RANKX(ALL('Table'[Emp],'Table'[Venatas Employee Wise]),'Table'[Venatas Employee Wise],,ASC)

Venatas Department Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Departamento]))

Rango de Emp en el Departamento1 = RANKX(FILTER('Table','Table'[Departamento] = EARLIER('Table'[Departamento])),'Table'[Venatas Employee Wise],,ASC)

Rango de Departamento de Emp1 = RANKX(ALL('Table'[Departamento],'Table'[Venatas Department Wise]),'Table'[Venatas Department Wise],,ASC)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)