Forum Discussion
Helper IIIRanking in columns
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
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 NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Since I had already started on these before seeing the post from Anonymous , 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
3 Replies
- mahoneypat
Microsoft Employee
Since I had already started on these before seeing the post from Anonymous , 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
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 NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
v-xuding-msftCommunity Support
Hi Truelearner ,
Are the answers helpful? If you make sense, please accept as solution. More people will benefit from this thread.