Forum Discussion
Ranking a specific value in a table
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!
Hi,
The RANK() function should help. Share the download link of the PBI file.
6 Replies
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
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?Hi,
The RANK() function should help. Share the download link of the PBI file.
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.
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.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.
