Working with Matrix with different levels..

[gomezc73]

Hi Experts,

   I am working with a data that have diferents level (Subcategories) to show the amounts in a matrix.

   By example, in a category only have two levels, other category 5 levels, etc.

  In the matrix I should show all the levels, but I know that the amounts in some categories are at level 2, and in others I should expand up to 5 levels to get the amounts.

  Is it possible in PBI to control that I can expand the levels only up to where the amounts are?

  By example:

   If i need see the amount of "Cost" i only need expand the matrix until level3 to see the amounts.

   If i need see the amount of  purchasing/China i need expand until the level 5 to see the amounts.

Level1	Level2	level3	level4	level5	code	Amount
Total Company	Accounting	Cost			A01	800.00
Total Company	Accounting	Audit			A02	992.00
Total Company	Accounting	Audit			A03	1,230.08
Total Company	Marketing	Social networks	Instagram		A04	1,525.30
Total Company	Marketing	Social networks	Twitter		A05	1,891.37
Total Company	Marketing	Other			A06	2,345.30
Total Company	Purchasing	Local			A07	2,908.17
Total Company	Purchasing	International	Asia	China	A08	3,606.13
Total Company	Purchasing	International	Asia	Japan	A09	4,471.61
Total Company	Purchasing	International	Europe		A10	5,544.79
Total Company	Manufacturing				A11	6,321.06
Total Company	Manufacturing				A12	7,206.01
Total Company	Manufacturing				A13	8,214.85
Total Company	Manufacturing				A14	9,364.93
Total Company	Sales	Florida	Miami		A15	10,676.02
Total Company	Sales	Florida	Miami		A16	6,875.54
Total Company	Sales	Florida	Orlando		A17	7,838.12
Total Company	Sales	Florida	Orlando		A18	8,935.45
Total Company	Sales	Georgia			A19	10,186.42
Total Company	Sales	Georgia			A20	11,612.51
Total Company	Sales	Georgia			A21	14,399.52

Any help wil be appreciate,

regards

[Irwan · Accepted solution]

hello @gomezc73 

something like this?

however, there are couple blank row because some rows only have value up to Level 2.

i am not sure but to create a matrix as your screenshot seem not possible at this moment.

there are levels that dont have value, for example, there are three level in Sales while there are only one level in Accounting.

So you would have a blank value in Accounting if you added until level 3 in row matrix to show value in Sale.

Visual filter seems not work as well because your blank value are placed differently in level.

Thank you.

[v-pgoloju]

Hi @gomezc73,

Thank you for reaching out to the Microsoft Fabric Forum Community.
And Thanks to @Irwan  for promt and helpful response

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[gomezc73]

Hi,

Thank you for the response, i was thinking if is possible to show a matrix like this:

[Irwan]

hello @gomezc73 

it should be possible using bookmark or drillthrough (although it need some reworks to do).

here i create a simple example using bookmark.

Hope this will help.

Thank you.

[gomezc73]

Hi,

  Thank you for your early response.

   Is not possible to have a matrix to show the data in this way?

[Irwan · Accepted solution]

hello @gomezc73 

something like this?

however, there are couple blank row because some rows only have value up to Level 2.

i am not sure but to create a matrix as your screenshot seem not possible at this moment.

there are levels that dont have value, for example, there are three level in Sales while there are only one level in Accounting.

So you would have a blank value in Accounting if you added until level 3 in row matrix to show value in Sale.

Visual filter seems not work as well because your blank value are placed differently in level.

Thank you.

[gomezc73 · Accepted solution]

clear

183 / 5.000

Resultados de traducción

Resultado de traducción

Exactly, that's the same result I am getting, and that's why I asked if any experts know a trick to prevent those blank levels from appearing. Thanks for the clarification.