Forum Discussion
Rank in columns
| Cat | Emp | Total Sales | Emp Individual Rank | Emp Deprt Wise Rank | Depart Rank |
| A | 1 | 10 | 1 | 1 | 1 |
| B | 2 | 20 | 2 | 1 | 1 |
| C | 3 | 30 | 3 | 2 | 2 |
| A | 4 | 40 | 4 | 2 | 2 |
| A | 5 | 50 | 5 | 3 | 2 |
| B | 6 | 60 | 6 | 2 | 1 |
| C | 7 | 70 | 7 | 2 | 2 |
I have data in above format and i want to find out ranks as Emp Individual Rank, Emp Deprt Wise Rank,Depart Rank
Note : I need these ranks to be create as caluclated columns not measures , as the agend of creating these ranks is to have a summarized rank table to have a workaround on RLS changing the ranking .
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
Hi Truelearner ,
Create 3 Calculated Columns
EMP IND RANK = RANKX('Table','Table'[Total Sales],,ASC,Dense)EMP DEPT RANK = RANKX(FILTER('Table','Table'[Cat] = EARLIER('Table'[Cat])),'Table'[Total Sales],,ASC,Dense)DEPT RANK = RANKX(ALL('Table'[Cat]),CALCULATE(SUM('Table'[Total Sales]),ALLEXCEPT('Table','Table'[Cat])))Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
4 Replies
Hi Truelearner ,
Create 3 Calculated Columns
EMP IND RANK = RANKX('Table','Table'[Total Sales],,ASC,Dense)EMP DEPT RANK = RANKX(FILTER('Table','Table'[Cat] = EARLIER('Table'[Cat])),'Table'[Total Sales],,ASC,Dense)DEPT RANK = RANKX(ALL('Table'[Cat]),CALCULATE(SUM('Table'[Total Sales]),ALLEXCEPT('Table','Table'[Cat])))Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Truelearner ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415amitchandak Thanks for sharing those links , with the help of them i am able to acheive first two requirements but not able to figureout how to write dax for third requirment (Overall department rank) , can you please take a look ?
Truelearner , I do not see a department in data. But looking at rank have a column like this
Dep Total Sales =
SUMX(
FILTER(
'Table',
'Table'[Depart]=EARLIER('Table'[Depart])
),
'Table'[Total Sales ]
)And create a Rank on it
RankX(all('Table'),[Dep Total Sales])
Do changes as per need
