Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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:
Regards,
Xiaoxin Sheng
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:
Regards,
Xiaoxin Sheng
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
User | Count |
---|---|
88 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |