RANKX has behaved every way but the right way

[Anonymous]

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

[v-zhangti]

Hi, @Anonymous 

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]

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.