Adjacent column groups in matrix

[Anonymous]

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:

instead of this:

Is this possible with Power BI at this moment?

Help is appreciated, Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

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 ()
    )
)

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

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

[Anonymous]

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

[themistoklis]

@Anonymous

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:

[Anonymous]

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.

[themistoklis]

@Anonymous

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.

[Anonymous]

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