Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Rank Formula Assistance

I am trying to rank companies based on their revenues in my data set.  It is a large data set with many rows of data for each company in a year and am looking for their rank based on revenue largest to smallest within the year I am filtered to across my Power BI file.  

 

Formulas I am using:

 

1 Rank = RANKX(ALL('table'[company_name]),[Revenue])

1 Walmart Rank = If ([Walmart Revenue] = 0,0,Calculate ([1 Rank],'table'[company_name]="Walmart"))

 

The goal is to have it return Walmarts rank across different product categories I have in the first column of my table.   (Ex:  Electronics in row 1, Apparel in row 2 - I want to see how walmart ranks by revenue compared to other companies for each category). 

 

The issue I am running into with my formulas is that Walmart is number one when I look at the raw data, but is showing up as number 2 when i use the '1 Walmart Rank' formula in my table.

 

Will appreciate any assistance that can be provided!

 

Thank You 

 

Need Help
Show and Tell
Tutorial Requests

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

     

    It would be more helpful for us to understand your purpose if you could share some dummy data and expected result. When a Rank formula is in a measure in the report or in a calculated column, it may return different results because the context for calculation is different. Also, when you only use ALL in the formula, the category will not be taken into account. 

     

    Best Regards,
    Community Support Team _ Jing