Matrix with 2 tables

[Anonymous]

https://advanprocommx-my.sharepoint.com/:u:/g/personal/eduardo_s_advanpro_com_mx/EWXlLcpE75FLhVXhC_B...

Hi Guys, Here is a link, please let me know if you could download it.

I have those 2 tables, The first one have all the accounts, so I put a filter, showing just the ones that have an "N" in the column "Acepta Datos". This is showing the fathers accounts, so I select a Matrix, and what I need is have it on levels, for example, the first level is the first number before the first "-", in this case the first level will have 10 rows (from 0 to 9), the second level should be selecting the "+" in the matrix and a list must go down with the next level, for example, if you select the row of number 1, a list will go down, showing the next level with all the accounts that begin with "1", but before the second "-", etc.  All the father accounts with the filter of "N" shouldnt have any amountm but the son accounts have a debit amount, so those should be the  sum of each level.

I hope I could explain myself

Kind regards

Eduardo Silva

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can add calculated columns to the second table to expand all levels and use them to create a hierarchy to use on matrix visual.

Steps:

1. Create calculated columns:

P0 =
PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 1 )

P1 =
PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 2 )

P2 =
PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 3 )

P3 =
PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 4 )

P4 =
PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 5 )

2. Create a matrix with the hierarchy field and value field(aggregate mode: sum)

3. Add 'table 1' [Acepta Datos] fields to the matxi visual level filter to filter records with 'N' tags.

Results:

Notice: your value fields seem saved as text type, you need to modify your value fields type to 'decimal number' to unlock the common aggregate features.

Regards,

Xiaoxin Sheng