Forum Discussion
RANKX ISSUES
I have been trying to iron out this issue for a week now. I saw in tutorials that if you use ALL in your RANKX formula that the Rank result would not be "Ones". I have done so in the below example and yet have "Ones" as the Rank result. What other things could be causing this?
Anonymous
Or with this
Ranking = RANKX ( SUMMARIZE ( ALLSELECTED ( 'WMGBU BEx Query' ), 'WMGBU BEx Query'[ENT BASE ITEM], 'WMGBU BEx Query'[profit center] ), CALCULATE ( SUM ( 'WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)] ) ) / CALCULATE ( SUM ( 'WMGBU BEx Query'[REVENUE (PROJECTED)] ) ) )
12 Replies
- Zubair_Muhammad
Community Champion
Anonymous
Try replacing ALL('WMGBU BEx Query') with ALLSELECTED('WMGBU BEx Query'[Customer])
Thanks that actually worked!!! So there is a part two to this question now. After I made your change I went to add in a column with the Profit Center name and then the rank changed to rank the results also by profit center...so there are multiple ones, twos, threes, etc. Is there a change that can be made in the formula so that it does not factor in the profit center on how it is ranking items?
- Zubair_MuhammadHi
Try with
ALL('WMGBU BEx Query'[Customer], 'WMGBU BEx Query'[profit center])
hthotaResolver III
The below link would help you to resolve the issue.
Link: http://community.powerbi.com/t5/Desktop/How-to-do-Ranking/td-p/342952
Alas this tells me everything that I have completed already. I have the rank appearing correctly now...my issue is if I want to add a profit center to the table (not a filter) the rank changes to by profit center as well. I need the rank regardless of the profit center which this thread does not address.
- Zubair_Muhammad
Anonymous
Try with
Ranking = RANKX ( ALL ( 'WMGBU BEx Query'[ENT BASE ITEM], 'WMGBU BEx Query'[profit center] ), CALCULATE ( SUM ( 'WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)] ) ) / CALCULATE ( SUM ( 'WMGBU BEx Query'[REVENUE (PROJECTED)] ) ) )