Dynamic ranking based on a matrix

[thaller]

Hi all,

I am rather new to PowerBI and I am trying to get a dynamic ranking. My current data is structured as follows:

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

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:

	Credit		MBS
	2019	2020	2019	2020	2021
BARCLAYS	1		2		2
BNP			1	2	1
JPM	2	1
RBC				1

Utlimately I would like to display the results by single broker in the report as follows

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

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 

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.

Any help would be appreciated

Thank you

Thomas

[amitchandak]

@thaller ,

rankx(allselected(Table[Broker]), calculate(Sum(Table[amount])))

rankx(filter(allselected(Table[Broker],table[Year]), [Year] =max([Year])), calculate(Sum(Table[amount])))

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
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...