Forum Discussion

unclebocky's avatar
unclebocky
Frequent Visitor
3 years ago
Solved

Column Order in Matrix - Show all columns for each value together

Is there an equivalent feature in Power BI to Excel pivot table sigma values field that would allow me to keep all columns for each value together in the matrix visual when displaying multiple values?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi unclebocky ,

     

    Currently, there seems no direct feature to achieve.

    Here's my workaround.

    Sample data

     

    1.Create a hierarchical table. I created by entering data.

    2.Create a measure that returns different types of results.

    Measure =
SWITCH (
    MAX ( 'Table (2)'[Type] ),
    "Premium",
        SUMX (
            FILTER ( 'Table', [Category] = MAX ( 'Table (2)'[Category] ) ),
            [Premium]
        ),
    "Sale", SUMX ( FILTER ( 'Table', [Category] = MAX ( 'Table (2)'[Category] ) ), [Sale] )
)

    Here's the result.

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

    • unclebocky's avatar
      unclebocky
      Frequent Visitor
      3 years ago

      Here is an example.  This is showing year of coverage on the rows and gender as a column field.  I am showing two value measures of sales and premium.  I instead want to show the two sales columns side by side and then the two premiums columns side by side for each gender.  If this was an excel pivot table, I could just use the "Σ Values" field to reorder the columns.  Is there an equivalent in PBI?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi unclebocky ,

         

        Currently, there seems no direct feature to achieve.

        Here's my workaround.

        Sample data

         

        1.Create a hierarchical table. I created by entering data.

        2.Create a measure that returns different types of results.

        Measure =
SWITCH (
    MAX ( 'Table (2)'[Type] ),
    "Premium",
        SUMX (
            FILTER ( 'Table', [Category] = MAX ( 'Table (2)'[Category] ) ),
            [Premium]
        ),
    "Sale", SUMX ( FILTER ( 'Table', [Category] = MAX ( 'Table (2)'[Category] ) ), [Sale] )
)

        Here's the result.

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.