Forum Discussion
Need a help on Ranking with Multiple Banks and multiple quarters
Hi Anonymous ,
How about try to use the build in treemap visual? It will show quarter with different color, then draw size for each bank based on the value.
Best regards,
Thank you, but this will not give the ranking unless we hover the mouse right?
I may not be able to compare with multiple banks' at a time that again a challenge here.
- Anonymous6 years agoNot applicable
There are more simple and straight forward solutions to your problem. But you said you are a beginner, so I will give a step by step solution to compare the rankings of banks across quarters in one shot.
Assuming that you have the table you have posted here in the Power BI model and the table name is "Bank Records",
Step 1: Create a calculated table named "Banke Ranking Table"
BanksRankingTable = ALL( BankRecords[Bank], BankRecords[ Quarter ] )Step 2: Add 3 calculated columns to the new table
Calculated column 1: Value
Value = SUMX ( FILTER ( BankRecords, AND ( BankRecords[Bank] = BanksRankingTable[Bank], BankRecords[ Quarter ] = BanksRankingTable[ Quarter ] ) ), BankRecords[ Value ] )Calculated Column 2:
BankRank = COUNTROWS ( FILTER ( BanksRankingTable, AND ( BanksRankingTable[ Quarter ] = EARLIER ( BanksRankingTable[ Quarter ] ), BanksRankingTable[Value] > EARLIER ( BanksRankingTable[Value] ) ) ) ) + 1Instead of using "EARLIER" function, you may also use Variables.
Calculated Column 3: Quarter as Text (You may avoid this if you want)
Qtr = "Q "&BanksRankingTable[ Quarter ]Step 3: Now add a "Matrix Visual" to your report.
Use "Bank Names" on rows, "Qtr" field on Columns and "Bank Rank" on the Values section of the matrix. Then add conditional formatting. The result will look like the visual above.
- v-lid-msft6 years ago
Community Support
Hi Anonymous ,
The size of each squares can compare the value of each bank and we can put the rank mearuse inside the tooltips, But if you want to show the rank value straightly, Anonymous 's solution is perfect and simple.
Best regards,