Forum Discussion
Need a help on Ranking with Multiple Banks and multiple quarters
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.
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.