Rank Percentage Value to display rank value for employees from a different table

[Anonymous]

Hello! 

I have a massive transactional table (Table 1) and a much smaller dimension table (Table 2).

Below is a screenshot showing the expected outcome along with sample data. 

Column A and B values are stored in Table 2 (Dimension table) and column C values are stored in a measure that I created in Table 1 (Transactional table). 

My current table visual in Power BI displays Column A, B, C values. 

Expected outcome is for me to display a column D with rank value as shown in the screenshot above (P.S. - screenshot is an example in excel, I need this in Power BI's table visual)

Rank value should be dynamic when I slice my data using any slicers/filters.

Rank is to be calculated using column C's amount variance percentage value so that I can view the rank value for each employee in column B. 

Kindly offer solutions to this problem. Thanks in advance!!

Edit: Tried a few RANKX formulas as follows and none of them gave me the desired outcome

Attempt1 -

RANK = RANKX(ALL('Table_1'), [Amount_Var%])

Attempt2 -

RANK_1 = RANKX(ALLSELECTED('Table_2'[EmployeeName]), Table_1[Amount_Var%])

Attempt3 -

RANK_2 = RANKX(ALLSELECTED('Table_1'[Amount_Var%]), Amount_Var%)

Attempt4 -

RANK_3 = RANKX(ALLSELECTED(Table_2[EmployeeName]), CALCULATE(Table_1[Amount_Var%]))

[Pravallika05]

Hi, 

I just wnat to check if you are able to achieve your result since i am also looking for similar logic.

Could you please post the DAX formula once you achieve the result

Regards,

Pravallika

BI analyst

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Share the link from where i can download your PBI file.

[Pravallika05]

 I am looking for values as of Expected Result column but currently i am getting values in Current Result column

Unfortunately, i am not supposed to upload pbix file or data since it is sensitive.

Below is the Dax i have coded

Rank:= if( not ISBLANK([AP Case Impact Score YTD PHC]), RANKX(ALLSELECTED('CenterUp Heirarchy'),[AP Case Impact Score YTD PHC],,ASC,Dense),BLANK())

[Ashish_Mathur]

Hi,

Does this measure work?

Rank:= if(ISBLANK([AP Case Impact Score YTD PHC]),BLANK(), RANKX(ALL('CenterUp Heirarchy'[Vendor]),[AP Case Impact Score YTD PHC],,ASC,Dense),BLANK())

Hope this helps.

[Pravallika05]

No, its now working

[Ashish_Mathur]

I cannot help you without seeing your file.

[selimovd]

Hey @Anonymous ,

for RANKX you have to select a column or a selection of a column. I'm not sure if you want to get the RANK by Manager or Employee or both.

I think the following measure should work:

Rank = RANKX( ALLSELECTED( 'Table_2'[EmployeeName]), [Amount_Var%] ) )

Or if you want the rank by both:

Rank = RANKX( ALLSELECTED( 'Table_2'[EmployeeName], 'Table_2'[ManagerName]), [Amount_Var%] ) )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd ,

Thank you for the formula, this generated a rank, however, not the rank I'm looking for. 

I used this formula now. 

Rank = RANKX( ALLSELECTED( 'Table_2'[EmployeeName]), [Amount_Var%] ) )

Instead of seeing a rank as follows,
1

2

3

4

5

6

I am seeing the following values instead. 

2

30

30

26

25

30

Is there a way to fix this such that my ranking based on employee, will appear as 

1

2

3

4

5

and so on? 

Kindly help! 

[selimovd]

@Anonymous can you post an actual screenshot from Power BI?

[Anonymous]

Hi @selimovd ,

Please see below screenshot

[v-yiruan-msft]

Hi @Anonymous ,

Please try to update the formula of Rank measure as below and check whether you can get the expected result:

Rank = RANKX(ALLSELECTED('Table_2'),CALCULATE([Amount_Var%]),,DESC,Dense)

Best Regards

[Anonymous]

Hi @v-yiruan-msft , 

Thanks for providing another formula! I tried this and did not give me the intended result. I had the ranking as 1, 3, 3, 3, 5, 2 etc. 

Do any other factors effect this formula? 

Kind regards

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide the related formula of measure [Amount_Var%] and some sample data from the involved fields? It is better if you can provide your sample pbix file(exclude sensitive data). Thank you.

Best Regards

[Anonymous]

@amitchandak, @parry2k , @Fowmy , @Jihwan_Kim , @selimovd 
Tagging you all as you appear to be top authors from last month on this thread. Please help!