cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonas_Jordao
Frequent Visitor

RANKX in Matrix Herarchy, but not in all levels.

Guys, good morning \ afternoon \ evening everyone.

I need help with the creation of a RANKX that i've been struggling to resolve this for a few days but so far I haven't been able to.

My database is a denormalized table with 31 columns, with only one dimension, a calculated table for calendar.

Sample
Image 1Image 1

My customer asks to create a Matrix that contains the following order:

- Category
- Top 5 Supplier Group by Spend
- All Top 5 Supplier Names
- All Items from Supplier Name

 

and this matrix will be filtered by some slicers that come from the same table.

*My rank needs to be applied only to suppliers by category, not names and items.*

In the first attempt I did a RANKX as it follows

Rank = RANKX (ALL (f_Data [Supplier - Group]), [Spend])

where the Spend measure is a sum of the column Spend


 Spend = SUM (f_Data [Spend])

and without filtering the Rank in the visual it worked kinda well,

 

Image 2Image 2

but when filtering, it gave a memory error. Then I changed the measure to look at the other columns:

Rank = CALCULATE (
RANKX (
ALL (f_Data [Supplier - Group])
, [Spend]),
ALL (f_Data [Supplier - Name]),
ALL (f_Data [Item - Description])
)

So there was no memory error, but the rank was wrong, and when I put Spend measure in the matrix values it is still wrong, and the rank brings in some categories only 4 and not 5.

 

Image 4Image 4

 

Image 3Image 3


I already tried several ways (separate table, ISINSCOPE and ISFILTERED in the measure, FILTER with ALL and ALLSELECT, etc.) and nothing worked the way it needs to.

If someone has already done something like this and can help me, i really appreciate it.

3 REPLIES 3
Jonas_Jordao
Frequent Visitor

Does anyone else have any idea how to do this? I'm out of ideas, any tips already help a lot... 

amitchandak
Super User
Super User

@Jonas_Jordao , Please try one of the two option and check if they can help

 

Rank = CALCULATE (
RANKX (
ALL (f_Data [Supplier - Group],f_Data [Supplier - Name],f_Data [Item - Description])
, [Spend])


or


Rank = CALCULATE (
RANKX (
ALL (f_Data)
, [Spend])

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hello @amitchandak , and thanks for the reply. I tried the rankx options, but unfortunately they didn't work. 


The first option - RANKX ALL 3 columns - gave the same error, didn't classify correctly

 

image002 (1).png

 

The second option - RANKX ALL f_Data - ranked the rank with 1 for all.

 

image001 (1).png

 

The sample table as requested is at the link (I left a dynamic table of how I need the result to be in the PBI):

 

Sample Data - Google Drive 

 

And thanks again for the help.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors