Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sri_phani
Helper III
Helper III

Show Rank in Card visual

Hi, 

 

I need help with creating a LOB-wise rank for the agent and a team-wise rank for the agent in the card.

Condition: The agent can change the LOB hence we have to check what is the agent's current week LOB and show the rank among the LOB agents for resolve attainment. Let's say a particular LOB has 200 agents. I have to show Rank among 200 agents. 

 

Tables: Active agents - 1: 1 - Metrics table. 

Slicers:  The page has 2 slicers' employee name and dates Between slicers. Slicers will have an impact on the card. 

Measures: 

Resolve = Resolve Numerator / Resolve Denominator

Resolve attainment = Resolve/0.6 (Target - 0.6)

Rank has to be based on Resolve attainment. Maximum the attainment higher the rank. 

 

Sample data - https://docs.google.com/spreadsheets/d/1eX4SpOAIbf8N35pzpBHKtqPmOzkud3ER-7DALQDWmFY/edit?usp=sharing

 

Tried creating a measure and Column as well. Both of them are not working. I checked multiple resources but couldn't understand how to derive the logic. 

1 ACCEPTED SOLUTION
Sri_phani
Helper III
Helper III
6 REPLIES 6
Sri_phani
Helper III
Helper III

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please try something like below whether it suits your requirement.

 

Rank: = 
RANKX(ALL('All Metrics One Place'[Employee Name],'All Metrics One Place'[Site + LOB]), [Resolve attainment],,DESC,Dense)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

No, this measure is not working. I tried something like this. This is working perfectly in table. but not in measure. 

Test rank =
VAR Tabled =
    SUMMARIZE(
                FILTER(
                    'All Metrics One Place',
                    'All Metrics One Place'[Date] <= MAX('All Metrics One Place'[Date]) &&
                    'All Metrics One Place'[Date] >= MIN('All Metrics One Place'[Date]) &&
                    'All Metrics One Place'[Site + LOB] = SELECTEDVALUE('All Metrics One Place'[Site + LOB])
                ),
                'All Metrics One Place'[Employee Name],
                'All Metrics One Place'[Site + LOB]
            ,
            "Resolve Numerator", SUM('All Metrics One Place'[Resolve Numerator]),
            "Resolve Denominator", SUM('All Metrics One Place'[Resolve Denominator]))
VAR newtable = ADDCOLUMNS(Tabled, "Resolve1", [Resolve Numerator]/[Resolve Denominator])
VAR test1 = ADDCOLUMNS(newtable, "attain", IF( [Resolve1]= BLANK(), BLANK(), [Resolve1]/0.60))
VAR Ranking = ADDCOLUMNS(test1,"Rank1", RANKX(test1, [attain],, DESC,Dense))
VAR test2 = FILTER(Ranking, 'All Metrics One Place'[Employee Name] = SELECTEDVALUE('All Metrics One Place'[Employee Name]))
VAR test3 = SELECTCOLUMNS(test2,"Rank2",[Rank1])

RETURN
test3

Hi @Sri_phani 

 

I think I have understood your request, are you trying to show the level based on the date and employee name selected by the slicer?

 

But could you please explain "The LOB must consider the current week. For example, Lucifer could have been in any LOB before, but the rank must be calculated based on the current week's LOB." ? How do you determine the LOB for the current week? is it based on the date?

 

Could you please give the original sample data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show it as screenshot. Please remove any sensitive data in advance.

 

Best Regards,
Yulia Xu

Sri_phani
Helper III
Helper III

@v-xuxinyi-msft Thank you for responding. 

 

I require to show only the rank number on card. Which has to be changed based on the slicer selected ( Employee Name, Dates).  

 

I gave the sample data here. As mentioned in below screenshot -  Lucifer got rank 2 when he was in safety. The LOB has to be considered for Current week. Example Lucifer can be in any LOB before but the rank has to be calculated based on the current week LOB.

 

Natalie_iTalent_0-1722874597674.png

 

 

The measure which I wrote is 

 

RANKX(ALLSELECTED('All Metrics One Place'[Employee Name],'All Metrics One Place'[Site + LOB]), [Resolve attainment],,DESC,Dense)

 

 
This works fine in table. It give very accurate result based on slicer what I select. 
 
But in card visual, it only shows 1 as rank. Please let me know if you need any further information. 
 
 
 
v-xuxinyi-msft
Community Support
Community Support

Hi @Sri_phani 

 

Would you need rank by Resolve attainment? And does the ranking need to change based on the value chosen by slicer?


This is my current test to rank by Resolve attainment.

 

Measure:

Resolve = DIVIDE(MAX([Resolve Numerator]), MAX([Resolve Denominator]))

 

Resolve attainment = [Resolve] / 0.6

 

rank = RANKX(ALL(Sheet1), [Resolve attainment], , ASC, Dense)

 

Then select Multi-row card visualization.

 

Output:

vxuxinyimsft_1-1722581951187.png

 

If this is not what you are looking for, then please explain your requirements in more detail, or you can present your desired results in Excel based on the sample data. Please remove any sensitive data in advance.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.