Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
@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)] ) ) )
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.
@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)] ) ) )
@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)] ) ) )
One last question...is there a way to add to this formula that will restrict the results to only the top 10?
Thx
I actually tried that with the Rank Measure and what would happen was from to month the viewable rows would change...10 one month, 8 another and sometimes only 3? That stumped me!
This one worked perfectly...thx so much for your time and efforts!
@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?
I tried to insert that into my formula and it errored out. Here is my actual formula....where should this be inserted ('WMGBU BEx Query'[profit center]) ?
Ranking = RANKX(ALLSELECTED('WMGBU BEx Query'[ENT BASE ITEM]),CALCULATE(SUM('WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)]))/CALCULATE(sum('WMGBU BEx Query'[REVENUE (PROJECTED)])))
Thx for your assistance!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |