Ranking a specific value in a table

[Creative_tree88]

Hi - I have a table called 'Company Performance'.  I need to produce a card visual which ranks my specific company, amongst all the other company's in the table.  I just need to display the rank number in the visual though.  This ranking will need to change depending on the selection of other filters which will affect the performance ranking as these filters will bring in other areas - my company might rank better in some areas, than others.  But, ultimately I just need to number in a card visual.

The ranking will be based on a calculation of performance, as follows:

Performance = Customers waiting within 6 weeks / Total number of Customers

So, somehow I need to incorporate this simple performance calculation into a rank.

Table Name = 'Company Performance'

Performance calculation = 'Performance = Customers waiting within 6 weeks / Total number of Customers'

Column in the table where company is located = 'Company Name'

Company Name = 'ABC Limited'

Can anyone please advise how I can write the RANK or RANKX formula accordingly, based on the above?  Many thanks!

[v-priyankata]

Hi @Creative_tree88 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Creative_tree88 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Creative_tree88 

Thanks @Ashish_Excel @pankajnamekar25 for your inputs, @Creative_tree88 May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[pankajnamekar25]

Hello @Creative_tree88 

Try this DAX code

ABC Limited Rank =

VAR SelectedCompany = "ABC Limited"

VAR RankedTable =

    ADDCOLUMNS(

        VALUES('Company Performance'[Company Name]),

        "PerformanceValue",

            DIVIDE(

                CALCULATE(SUM('Company Performance'[Customers waiting within 6 weeks])),

                CALCULATE(SUM('Company Performance'[Total number of Customers]))

            )

    )

VAR CompanyRank =

    RANKX(

        RankedTable,

        [PerformanceValue],

        ,

        DESC,  -- Higher performance ranks better

        DENSE

    )

RETURN

    CALCULATE(

        CompanyRank,

        'Company Performance'[Company Name] = SelectedCompany

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Creative_tree88]

@pankajnamekar25  - thank you. However, I'm getting the following error message:

The value for 'PerformanceValue' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

Any ideas?

[Ashish_Excel · Accepted solution]

Hi,

The RANK() function should help.  Share the download link of the PBI file.