Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
94 | |
88 | |
70 |
User | Count |
---|---|
166 | |
133 | |
129 | |
102 | |
98 |