Help with RANKX

[Anonymous]

Hello All

Been at this for hours and nothing working. I have 3 columns (VMName, Memory, Date). The Memory column shows how much memory has been used for each Virtual Machine by month. The "date" is a calculated column which has month and year.

I just want to show the top 5 Virtual Machines for each month "by usage," which is the Memory column. I tried 2 measures to sum the memory and then Rank by VM Name and not working, I get tons of rows with different values ranked the same (all 1 or all 2) and of course Top N not filtering anything correctly. I even tried Top N on the Memory field just to get the top 5 values and it gives a full list. Can anyone recommend a simple way of doing this?

[Anonymous]

Attached is a sample copy of my data. I simply need a rank of VM's which have the highest memory each month. The rank column shown here is what I need it to look like.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link

All measures are in the sample pbix file.

https://www.dropbox.com/s/p8v3eb5sksem6cd/user.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thanks Jihwan-----my date field reads Nov 2020, Dec 2020, Jan 2021 (etc). My months go all the way back to Jan 2020. How would I use your formula to rank all within my date format for month and year. Top 5 for all rows that say Nov 2020, Top 5 for all rows that say Dec 2020.......and so on? I appreciate your help.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think you can just put the Month & Year Column from your date table.

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thanks----a picture of the sample data is here. Can you give me an example of the dax?

I simply need a rank of VM's which have the highest memory each month (using VM Name and Memory colummns). The rank column shown here is what I need it to look like.

[Jihwan_Kim]

Hi, @Anonymous 

Sorry that I quite do not understand your question.

I think my sample is already giving what you are looking for.

I think I am missing something.

That is why I asked to provide the sample pbix file's link here, not a screenshot.

[Anonymous]

Sorry Jihwan, I got it to work. Thanks for your help!

[amitchandak]

@Anonymous You can create a rank like

rankx(allselected(Table[VMName]), calculate(sum(Table[Memeory])))

in the visual if you take any column/ungrouped field, the rank will happen inside that/those column/columns

You can have month also included (have month year column in table)

rankx(filter(allselected(Table[VMName],Table[month year]),[month year] =max([month year])) , calculate(sum(Table[Memeory])))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[Anonymous]

Thanks----I tried your sugggestion for calculated column, but the rankings just return as "1" for every row (all months and years). I used my column titled "DATE" which contains month and year in this format: Jan 2021.