Top 15 Values

[EpicTriffid]

Hi all,

I'm really beginning to lose my rag with RANKX and TOPN, or at least in trying to work out what I'm doing.

I want to be able to isolate the top 15 rows in my table, including the top 15 of any filtered results:

So the user can filter on Provider or Course Name on the page, and I only ever want it to show the top 15 of the returned rows. using a TOPN filter on Course or Provider Name does nothing, so I've tried to use RANKX, RANK, and TOPN, with the only tangible results that aren't just infinite 1's coming from RANKX with the following code:

Ranking = RANKX(ALLSELECTED('54'[Course name],'54'[2023]), CALCULATE(SUM('54'[2023])))

This gives me the following result (far left column): 

Absolutely no idea what's going on here!

Can anyone shed some light on this, on what I might be missing in my understanding? Cheers!

[consvax]

Not going to lie. I have no advice as I have been in the same boat for 40 hours now.  I do not undstand why we can't use SQL to create some views in Power BI because DAX gets real messy.

I am trying to get the Top 5 ranked builds off of my Top 15 categories ranked by sales$'s and no matter what I do the data change when I change the format of the visual.  Meaning including a measure or not.  

EXAMPLE:

Build Rank on Top 15 =

RANKX(

    FILTER(ALL(Gender_Category[Gender Category]), [CategoryRankLastWeek] <= 15 ),

    [Build],

    ,

    DESC,

    Dense

)

It's NOT USING MY TOP 15!  And why is "4" repeated so much when those numbers it is based off not even close to the same value???

[Anonymous]

Hi, @EpicTriffid 

Rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[2023]),ALLSELECTED('Table'[Provider name 2023],'Table'[Course name])),,DESC)

Is this the result you were hoping for? If not, can you provide example data and a description of your desired output?

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.

[Bibiano_Geraldo]

1- Create a Measure to sum your 2023 column

SumOf2023Column = 
SUM(
    '54'[2023]
)

2- Create Other Measure to rank your table

Ranking= 
RANKX(
    ALL('54'[Course name], 
    [SumOf2023Column] , , 
    DESC, 
    Skip
)

[shafiz_p]

Hi @EpicTriffid  This is my data. I want to find top 15 CS names by # of orders. To find rank try below code:

Rank = 

RANKX(
    ALLSELECTED('CS Data'),
    [# of Order],
    ,
    DESC,
    DENSE
)

To filter out only top 15, try below code:

Top 15 = 
IF([Rank] <= 15, 1, BLANK())

Now use this measure in filter this visual option. See image below:

Here is the top 15. Now you can filter using any dimension.

Hope this helps!!

If this solved you problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[EpicTriffid]

Sorry. Thankyou for the suggestion but the result in the same

[uzuntasgokberk]

Hello @EpicTriffid ,

Can you change the dax code:

test =

RANKX(ALLSELECTED(RANKTEST[Course name]),CALCULATE(SUM(RANKTEST[2023])),,DESC,Dense)

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

[EpicTriffid]

Thank you for the suggestion, but the results are the same as the second screenshot I posted above. Ranking just doesn't seem to want to work on this data. To be clear, there are no additional filters on the table beyond the columns that you can see. The columns are Provider, Course Name, 2019, 2020, 2021, 2022, 2023.

[uzuntasgokberk]

Hello @EpicTriffid,

Sorry I forgot to add another your text column. Could you please use below the code:

test =

RANKX(ALLSELECTED(RANKTEST[Coursename],RANKTEST[ProviderName]),CALCULATE(SUM(RANKTEST[2023])),,DESC,Dense)

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |