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
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
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,
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.
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.
Does anyone else have any idea how to do this? I'm out of ideas, any tips already help a lot...
@Anonymous , 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.
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
The second option - RANKX ALL f_Data - ranked the rank with 1 for all.
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):
And thanks again for the help.
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 |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |