Need a help on Ranking with Multiple Banks and multiple quarters

[Anonymous]

Hi Team,

I am new to this power BI, I have a request where they are looking for a ranking chart which has multiple banks (20+ Banks) and multiple quarters (3 Years of Quarters selection).

What is the best way to provide the ranking charts... which they can use it for comparing for each bank for quarter better way.. any custom visuals which good for Ranking Chart please suggest.

Any help with this solution is greatly appreciated.

Bank	Date	Value	Quarter
Bank3	1/2/19 12:00 AM	12,501,151.25	1
Bank3	1/2/19 12:00 AM	12,454,331.36	1
Bank12	1/2/19 12:00 AM	73,578,103.16	1
Bank12	1/2/19 12:00 AM	73,624,072.83	1
Bank9	1/2/19 12:00 AM	62,992,500.00	1
Bank9	1/2/19 12:00 AM	63,065,450.00	1
Bank3	1/2/19 12:00 AM	170,237,400.00	1
Bank3	1/2/19 12:00 AM	170,588,700.00	1
Bank12	1/2/19 12:00 AM	1,484,466.91	1
Bank10	4/1/19 12:00 AM	5,231,767.85	2
Bank10	4/1/19 12:00 AM	5,235,370.70	2
Bank10	4/1/19 12:00 AM	2,209,822.66	2
Bank10	4/1/19 12:00 AM	2,213,131.78	2
Bank8	4/2/19 12:00 AM	52,066,721.17	2
Bank8	4/2/19 12:00 AM	52,154,441.53	2
Bank7	4/2/19 12:00 AM	781,201.87	2
Bank7	4/2/19 12:00 AM	781,731.93	2
Bank6	4/2/19 12:00 AM	1,236,427.99	2
Bank6	4/2/19 12:00 AM	1,210,678.67	2
Bank8	4/2/19 12:00 AM	89,459,680.00	2
Bank8	4/2/19 12:00 AM	89,626,024.00	2
Bank10	7/1/19 12:00 AM	1,118,486.91	3
Bank10	7/1/19 12:00 AM	1,120,069.18	3
Bank1	7/1/19 12:00 AM	16,943,550.00	3
Bank1	7/1/19 12:00 AM	16,974,822.00	3
Bank3	7/2/19 12:00 AM	10,000,000.00	3
Bank1	7/2/19 12:00 AM	47,800,000.00	3
Bank1	7/2/19 12:00 AM	47,800,000.00	3
Bank12	7/2/19 12:00 AM	262,222.05	3
Bank12	7/2/19 12:00 AM	261,271.31	3
Bank12	7/2/19 12:00 AM	885,197.03	3
Bank12	7/2/19 12:00 AM	874,734.82	3
Bank3	7/3/19 12:00 AM	8,855,892.49	3
Bank3	7/3/19 12:00 AM	8,858,958.73	3
Bank2	7/3/19 12:00 AM	5,591,408.00	3
Bank7	7/3/19 12:00 AM	3,524,437.96	3
Bank7	7/3/19 12:00 AM	3,842,922.33	3
Bank2	10/1/19 12:00 AM	5,061,092.45	4
Bank2	10/1/19 12:00 AM	5,069,320.42	4
Bank2	10/1/19 12:00 AM	2,585,325.06	4
Bank2	10/1/19 12:00 AM	2,588,771.86	4
Bank2	10/1/19 12:00 AM	87,465,528.00	4
Bank2	10/1/19 12:00 AM	87,635,200.00	4
Bank1	10/1/19 12:00 AM	55,108,332.00	4
Bank1	10/1/19 12:00 AM	55,166,998.50	4
Bank7	10/1/19 12:00 AM	2,929,321.33	4
Bank7	10/1/19 12:00 AM	2,935,227.41	4
Bank9	10/2/19 12:00 AM	10,000,000.00	4
Bank2	10/2/19 12:00 AM	26,293,177.34	4
Bank2	10/2/19 12:00 AM	26,301,728.93	4
Bank4	10/3/19 12:00 AM	1,600,000.00	4
Bank12	10/3/19 12:00 AM	30,991,250.00	4

[v-lid-msft]

Hi @Anonymous ,

We can try to use a Bulid-in Funnel, it will show the value based on the desc order, then we can use a slicer to spefic the quarter.

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

Best regards,

[Anonymous]

Thanks for your time for help.

yes, this looks fine when you need to compare against only one quarter with all the banks.

However, the request is to compare multiple banks against multiple quarters. is it possible?

[Anonymous]

Any help please?

[v-lid-msft]

Hi @Anonymous ,

Sorry for delay in our response, Do you want to have multi chart for each quarter of year or compare them inside one chart with value of all quarters?

Best regards,

[Anonymous]

One chart would help. however, if no option at all then needs to see on multi-chart.

Here mainly we are looking at how the ranking changes based on the amount for each month/quarter so that it help to compare against bank.

[v-lid-msft]

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,

[Anonymous]

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.

[v-lid-msft]

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,

[Anonymous]

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] )
        )
    )
) + 1

Instead 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.