cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mm87 Frequent Visitor
Frequent Visitor

Adjacent column groups in matrix

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

tabel.png

 

 

 

 

 

 

 

 

Must look like this:

Moetzijn.png

 

instead of this:

Fout.png

 

Is this possible with Power BI at this moment?

 

Help is appreciated, Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
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],
            [Stad],
            [geslacht],
            "leeftijdgroep", [geslacht],
            "Aantal", SUM ( 'Sample'[Aantal] )
        ),
        "Client", BLANK (),
        "Land", [Land],
        "Stad", [Stad],
        "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 ()
    )
)

23.png

 

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

22.png

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
themistoklis New Contributor
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:

 

image.png

mm87 Frequent Visitor
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 Smiley Happy

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

 

themistoklis New Contributor
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.

mm87 Frequent Visitor
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

Highlighted
Community Support Team
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],
            [Stad],
            [geslacht],
            "leeftijdgroep", [geslacht],
            "Aantal", SUM ( 'Sample'[Aantal] )
        ),
        "Client", BLANK (),
        "Land", [Land],
        "Stad", [Stad],
        "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 ()
    )
)

23.png

 

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

22.png

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: | |
mm87 Frequent Visitor
Frequent Visitor

Re: Adjacent column groups in matrix

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