Top 3 customers list (Receipts and Payments) based on date range

[vkboddapati]

Hellow Everyone,
Need help! I have data like below (sample data):
Date |         Doc No|      Trans Type|  Client Code| Bank Account | Debit |  Credit  | Balance 

01-Jan-22	13920	B. Payments	678615	1470	-	1,622.42	-1,622.42
01-Jan-22	13922	B. Payments	673221	1470	-	155.26	-155.26
01-Jan-22	13924	B. Payments	690301	1470	-	493.42	-493.42
01-Jan-22	13925	B. Payments	692250	1470	-	1,536.06	-1,536.06
03-Jan-22	13897	B. Payments	639407	2003	-	1,180.00	-1,180.00
03-Jan-22	13898	B. Payments	600265	2003	-	590.00	-590.00
03-Jan-22	13937	B. Payments	672565	1461	-	38,613.00	-38,613.00
03-Jan-22	13977	B. Payments	690623	2003	-	1,23,318.00	-1,23,318.00
05-Jan-22	13968	B. Payments	627678	1461	-	43,52,622.00	-43,52,622.00
05-Jan-22	13969	B. Payments	687682	1461	-	10,732.00	-10,732.00
05-Jan-22	13974	B. Payments	652938	2003	-	590.00	-590.00
06-Jan-22	14037	B. Payments	668442	2003	-	1,180.00	-1,180.00
07-Jan-22	14093	B. Payments	627394	1470	-	2,748.33	-2,748.33
10-Jan-22	14340	B. Payments	686831	1461	-	15,04,580.00	-15,04,580.00
10-Jan-22	14342	B. Payments	607267	1461	-	30,26,759.00	-30,26,759.00
10-Jan-22	14343	B. Payments	671732	1461	-	49,40,008.00	-49,40,008.00
10-Jan-22	14344	B. Payments	687696	1461	-	47,076.00	-47,076.00
01-Jan-22	13841	B. Payments	517244	1461	14,43,159.00	-	14,43,159.00
03-Jan-22	13929	B. Payments	554258	1470	4,60,00,000.00	-	4,60,00,000.00
03-Jan-22	14955	B. Payments	536050	1481	1,75,680.54	-	1,75,680.54
10-Jan-22	14262	B. Payments	504250	1461	1,20,00,000.00	-	1,20,00,000.00
10-Jan-22	14265	B. Payments	528789	1461	1,50,00,000.00	-	1,50,00,000.00
11-Jan-22	2739	A. Receipts	544496	1468	14,70,431.56	-	14,70,431.56
13-Jan-22	2888	A. Receipts	565153	1453	88,99,590.00	-	88,99,590.00
13-Jan-22	14490	B. Payments	521423	1461	50,00,000.00	-	50,00,000.00
14-Jan-22	2629	A. Receipts	564181	1453	31,17,306.00	-	31,17,306.00
20-Jan-22	2650	A. Receipts	522831	1481	65,39,226.12	-	65,39,226.12
01-Jan-22	2553	A. Receipts	522775	2003	3,16,58,175.00	-	3,16,58,175.00

and I want the output as below:

Please help me with relavant suggessions and functions, I have been trying with TopN but couldn't succeed.
Thanks in Advance,
VKB

[v-shex-msft · Accepted solution]

HI @vkboddapati,

You can do unpivot columns on the 'Debit, Credit, Balance' fields to convert them to attribute and vlaue.

Unpivot columns (Power Query) (microsoft.com)

Then you can create a new parameter table with all category types that you want to use and use them on matrix row fields, raw table bank account on column fields. 

After these steps, you can create a measure formula with switch function to check the current category and redirect to different calculation expressions:

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Regards,

Xiaoxin Sheng

[vkboddapati]

Many Thanks, @v-shex-msft!  
I got the desired result!

However, got an additional requirement of "Opening" & "Closing" balances in the same matrix on which I'm working, below is the link of it:   
https://community.powerbi.com/t5/Desktop/Dynamic-Opening-and-Closing-Balances-along-with-TopN-based-...

Regards,

VKB