cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rleseberg
Frequent Visitor

RANKX has behaved every way but the right way

Hey community, could really use your help with this.

My database has rows of transactions. I am trying to rank them based on their average margin in a table visual, by state.

Because I am pulling from different data sources for the same visual, I created a State dimension table to connect my transactions with another fact table (that gives me other data I need by state). I have been unsuccessful using the State field from both my State table and my transaction table.

The following equations have not worked:

Rank Margin % = RANKX(ALL(Table[State]),CALCULATE([Margin % Measure]))    - Returns 1's and 2's, no discernible pattern.

Rank Margin % = RANKX(ALLSELECTED(Table[State]),CALCULATE([Margin % Measure]))    - Returns 1's and 2's

Rank Margin % = RANKX(ALLSELECTED(Table[State]),CALCULATE([Margin % Measure]),,ASC,Dense) - Returns 2's and 3's

Rank Margin % = RANKX(ALLSELECTED(Table[State]),CALCULATE([Margin % Measure]),,ASC,Skip) - Returns 43's. I think there are 44 states in my database, with only 25 in the visual.

The measure I am ranking on was initially just an average of the column, then the measure by itself, and now the CALCULATE(Measure) also doesn't work.

Please let me know if you can help! Appreciate it.

Thanks
2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @rleseberg 

 

Can you provide simple EXCEL data or simple PBIX files for testing? What kind of results do you expect? Is it possible to provide pictures for reference. Looking forward to more details you can provide, thank you.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

Does this work?

Rank Margin % = RANKX(ALLSELECTED(Table[State]),[Margin % Measure])

If not, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors