cancel
Showing results for
Did you mean:
Frequent Visitor

I want to make a matrix with with row groups wich are a hierarchy, but the column groups must be adjacent

For example, this table

Must look like this:

Is this possible with Power BI at this moment?

Help is appreciated, Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: Adjacent column groups in matrix

Hi @mm87,

You can refer to following calculate table formula to create a new table with original table records and summarize table records:

```Table =
UNION (
'Sample',
SELECTCOLUMNS (
SUMMARIZE (
'Sample',
[Land],
[geslacht],
"leeftijdgroep", [geslacht],
"Aantal", SUM ( 'Sample'[Aantal] )
),
"Client", BLANK (),
"Land", [Land],
"geslacht", [geslacht],
"leeftijdgroep", [leeftijdgroep],
"Aantal", [Aantal]
)
)
```

After these, you can create a table as custom sort order table to define column order.(you can't direct change sort order of matrix column fields)

```Sort Table =
SELECTCOLUMNS (
VALUES ( 'Table'[leeftijdgroep] ),
"leeftijdgroep", [leeftijdgroep],
"Index", SWITCH (
[leeftijdgroep],
"M", 1,
"V", 2,
"18-30", 3,
"31-50", 4,
"51-65", 5,
BLANK ()
)
)
```

Build relationship from new table to sort table, then create matrix visual.

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
6 REPLIES 6
New Contributor

Re: Adjacent column groups in matrix

@mm87

On Matrix Trable, put Land as rows and as Columns put Geslacht and Leeftijdgroep.

Then on the matrix object on the top left click on the following icon:

Frequent Visitor

Re: Adjacent column groups in matrix

Thanks for your response, but this gives me the output like my screenshot that i dont want as a result

The leeftijd and geslacht work like a hierarchy, for every "geslacht" it gets sliced in leeftijdsgroepen.

New Contributor

Re: Adjacent column groups in matrix

@mm87

Sorry my mistake....

I dont think you can do this with a matrix.

What you can do though is create 5 new measures for each one of the metric fields and add them on a table and not matrix.

Frequent Visitor

Re: Adjacent column groups in matrix

ye was afraid it wasnt possible yet, to bad.

Your solution with the measures is a nice idea, but we work with a corporate tabular model so in our case it wouldt be a solution to create a lot of measures just for 1 visual (in reality it are few more columns we need)

Thanks again for the effort though

Community Support Team

Re: Adjacent column groups in matrix

Hi @mm87,

You can refer to following calculate table formula to create a new table with original table records and summarize table records:

```Table =
UNION (
'Sample',
SELECTCOLUMNS (
SUMMARIZE (
'Sample',
[Land],
[geslacht],
"leeftijdgroep", [geslacht],
"Aantal", SUM ( 'Sample'[Aantal] )
),
"Client", BLANK (),
"Land", [Land],
"geslacht", [geslacht],
"leeftijdgroep", [leeftijdgroep],
"Aantal", [Aantal]
)
)
```

After these, you can create a table as custom sort order table to define column order.(you can't direct change sort order of matrix column fields)

```Sort Table =
SELECTCOLUMNS (
VALUES ( 'Table'[leeftijdgroep] ),
"leeftijdgroep", [leeftijdgroep],
"Index", SWITCH (
[leeftijdgroep],
"M", 1,
"V", 2,
"18-30", 3,
"31-50", 4,
"51-65", 5,
BLANK ()
)
)
```

Build relationship from new table to sort table, then create matrix visual.

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Highlighted
Frequent Visitor

Re: Adjacent column groups in matrix

Thank you, it looks like this is going to solve the problem!