Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |