Matrix table column formatting

Power BI
1

My query is related to the matrix table formatting.
I am creating some matrix tables in which there are multiple GL Codes under Credit and Debit. When I’m putting the Debit and Credit amounts in Values field of matrix table two sub-columns are created under the GLs codes column ( Debit & Credit).

I want to have a Credit and Debit Columns at 1st level in the columns field and below the Credit and Debit columns respective GLs amount.

Screenshot_2022-09-07-00-08-22-079_com.anydesk.anydeskandroid
Screenshot_2022-09-07-00-08-22-079_com.anydesk.anydeskandroid1080×2340 241 KB

2

Hi @Abhijeet357, from my understanding of your problem…

  • You have created 2 separate Credit and Debit measures
  • You would like these to show up first as column headers, above your GL Codes

The quickest way to work with what you have is:

1. Create a dimension for Credit/Debit
Example:
image

2. Create a single measure to drop into your matrix

Amount Value = 
SWITCH(
    MAX(UILabels[UI_Heading]),
    "Credit", [YourCreditsMeasure],
    "Debit", [YourDebitsMeasure]
)

3. Then just drop your headings in as a dimension before GL.
image

Of course, if your GLs are specifically only attached to Debits or Credits, then you could alternatively use Account Type mapping against your GLs, and use that as a dimension instead, but I doubt this would be the case.

Let me know how you go.

Cheers
Michelle :v:

1 Like
3

Thanks a lot for solution.
I have one follow up query . Actually these GLs are related to Payroll activity therefore due to retro accounting, some of the GLs might have both types of amount i.e. credit and debit. So I need to have a Net credit in the Receipt GLs and Net Debit in Payment GLs. I have a separate dimension table of GLs where each GL is classified into Receipt and Payment GL.

4

No worries @Abhijeet357 .

I’m not sure if I am following your second query - might need you to expand further on what you’re specifically need help with?

5

Plase check this
image

The GL has both amounts in Credit as well as Debit. I want only Net Debit amount for this GL . As it is s payment/expenditure GL.

6

Resolved this by creating conditional column in Power Query.

1 Like