Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hi
I have table 1 is power BI. I wanna create a matrix like table 2 with below logic:
- Rank by PL & Route: Ranking the Amt by PL Cat & Route
- Rank by Item: Ranking the Amt by Item only
So, How to make a measure to do it?
Solved! Go to Solution.
@ngocnguyen , Try ranks like
ranxk(allselected(Table[Item]), calculate(sum(Table[Amount]), allexcept(Table, Table[Item])),,desc,dense)
ranxk(Summarize(allselected(Table),Table[PL cat],Table[item] ), calculate(sum(Table[Amount]), allexcept(Table, Table[PL cat], Table[item])),,desc,dense)
Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Hi @ngocnguyen ,
According to your description, here's my solution. Create calculated columns.
Rank by PL&Route =
RANKX (
'Table 1',
SUMX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PL Cat] = EARLIER ( 'Table 1'[PL Cat] )
&& 'Table 1'[Route] = EARLIER ( 'Table 1'[Route] )
),
'Table 1'[Amt]
),
,
DESC,
DENSE
)
For the second "Rank by Item", your requirement is Ranking the Amt by Item only, but the result in Table2 is not ranking by item. So I provide two formula here.
If you really want to rank by Item:
Rank by Item =
RANKX ( 'Table 1', 'Table 1'[Item],, ASC )
If you want to get the provided result:
Column =
RANKX (
FILTER (
'Table 1',
'Table 1'[Route] = EARLIER ( 'Table 1'[Route] )
&& 'Table 1'[PL Cat] = EARLIER ( 'Table 1'[PL Cat] )
),
'Table 1'[Amt]
)
Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ngocnguyen ,
According to your description, here's my solution. Create calculated columns.
Rank by PL&Route =
RANKX (
'Table 1',
SUMX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PL Cat] = EARLIER ( 'Table 1'[PL Cat] )
&& 'Table 1'[Route] = EARLIER ( 'Table 1'[Route] )
),
'Table 1'[Amt]
),
,
DESC,
DENSE
)
For the second "Rank by Item", your requirement is Ranking the Amt by Item only, but the result in Table2 is not ranking by item. So I provide two formula here.
If you really want to rank by Item:
Rank by Item =
RANKX ( 'Table 1', 'Table 1'[Item],, ASC )
If you want to get the provided result:
Column =
RANKX (
FILTER (
'Table 1',
'Table 1'[Route] = EARLIER ( 'Table 1'[Route] )
&& 'Table 1'[PL Cat] = EARLIER ( 'Table 1'[PL Cat] )
),
'Table 1'[Amt]
)
Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@ngocnguyen , Try ranks like
ranxk(allselected(Table[Item]), calculate(sum(Table[Amount]), allexcept(Table, Table[Item])),,desc,dense)
ranxk(Summarize(allselected(Table),Table[PL cat],Table[item] ), calculate(sum(Table[Amount]), allexcept(Table, Table[PL cat], Table[item])),,desc,dense)
Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
71 | |
65 | |
42 | |
42 |
User | Count |
---|---|
49 | |
42 | |
29 | |
28 | |
27 |