cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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
Helper III
6 REPLIES 6
Helper III
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.

Helper III

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
Community Support

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

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.

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.

Community Support

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:

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors