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
