Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

 

 

 

 

 

 

 

Must look like this:

 

instead of this:

 

Is this possible with Power BI at this moment?

 

Help is appreciated, Thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        themistoklis
        Community Champion
        7 years ago

        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.