Show Rank in Card visual

[Sri_phani]

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. 

[Sri_phani · Accepted solution]

I got the answer in this thread - Refer to this thread https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cannot-Identify-the-table-that-conta...

[Jihwan_Kim]

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)

[Sri_phani]

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

[Anonymous]

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]

@Anonymous 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. 

[Anonymous]

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:

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.