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:   ins...
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

themistoklis's avatar
themistoklis
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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