Dynamic ranking based on a matrix

Question

Hi all,&nbsp;I am rather new to PowerBI and I am trying to get a dynamic ranking. My current data is structured as follows:Trade IDDateAsset ClassBrokerTrade amountX0000110.01.2019CreditBARCLAYS&nbsp;&nbsp;&nbsp;&nbsp; 50 000 000.00X0000220.06.2019CreditBARCLAYS&nbsp;&nbsp;&nbsp;&nbsp; 25 000 000.00X0000320.01.2020CreditJPM&nbsp;&nbsp;&nbsp;&nbsp; 15 000 000.00X0000420.01.2020CreditJPM&nbsp;&nbsp;&nbsp;&nbsp; 20 000 000.00X0000530.03.2020MBSRBC&nbsp;&nbsp;&nbsp;&nbsp; 60 000 000.00X0000619.12.2019MBSBNP&nbsp;&nbsp; 800 000 000.00X0000721.02.2021MBSBARCLAYS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 000 000.00X0000804.03.2020MBSBNP&nbsp;&nbsp;&nbsp;&nbsp; 40 000 000.00X0000910.01.2019CreditJPM&nbsp;&nbsp;&nbsp;&nbsp; 50 000 000.00X00001020.06.2019MBSBARCLAYS&nbsp;&nbsp;&nbsp;&nbsp; 25 000 000.00X00001120.01.2020CreditJPM&nbsp;&nbsp;&nbsp;&nbsp; 15 000 000.00X0000721.02.2021MBSBNP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6 000 000.00&nbsp;I would like to be able to rank each brokers within each asset class for each different year (e.g. BARCLAYS is best broker in 2019 for Credit). The criterion for ranking would be the sum of trade values (highest sum = highest ranking). As an intermediary result I think the matrix should look like the following:&nbsp;Credit&nbsp;MBS&nbsp;&nbsp;&nbsp;20192020201920202021BARCLAYS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2BNP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1JPM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;RBC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;Utlimately I would like to display the results by single broker in the report as followsBarclays201920202021Credit100MBS202&nbsp;A contraint is that I need to keep these rankings dynamic as the user can:- determine the time frame (e.g. ranking only on the time period 07/2019-06/2020)- determine which broker to integrate&nbsp;Therefore I don't find it possible to implement a ranking by measures at the database level because it is then unaffected by the time slider.&nbsp;Any help would be appreciatedThank youThomas

amitchandak · Answer

thaller , 
&nbsp;
rankx(allselected(Table[Broker]), calculate(Sum(Table[amount])))
&nbsp;
rankx(filter(allselected(Table[Broker],table[Year]), [Year] =max([Year])), calculate(Sum(Table[amount])))
&nbsp;
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finalehttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
&nbsp;

Forum Discussion

Dynamic ranking based on a matrix

1 Reply

Related content

Dynamic Ranking

Dynamic Ranking

Dynamic Ranking

Dynamic Ranking

Dynamic Rank Calculation

Trade ID	Date	Asset Class	Broker	Trade amount
X00001	10.01.2019	Credit	BARCLAYS	50 000 000.00
X00002	20.06.2019	Credit	BARCLAYS	25 000 000.00
X00003	20.01.2020	Credit	JPM	15 000 000.00
X00004	20.01.2020	Credit	JPM	20 000 000.00
X00005	30.03.2020	MBS	RBC	60 000 000.00
X00006	19.12.2019	MBS	BNP	800 000 000.00
X00007	21.02.2021	MBS	BARCLAYS	3 000 000.00
X00008	04.03.2020	MBS	BNP	40 000 000.00
X00009	10.01.2019	Credit	JPM	50 000 000.00
X000010	20.06.2019	MBS	BARCLAYS	25 000 000.00
X000011	20.01.2020	Credit	JPM	15 000 000.00
X00007	21.02.2021	MBS	BNP	6 000 000.00

Barclays	2019	2020	2021
Credit	1	0	0
MBS	2	0	2