Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
unclebocky
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Hi @unclebocky ,

 

Currently, there seems no direct feature to achieve.

Here's my workaround.

Sample data

vstephenmsft_1-1663824963047.png

 

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

vstephenmsft_0-1663824943499.png

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.

vstephenmsft_2-1663825084468.png

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.

 

View solution in original post

3 REPLIES 3
VijayP
Super User
Super User

@unclebocky 

Any example?! will help to look at the point!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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?

 

PBI Ex.png

Hi @unclebocky ,

 

Currently, there seems no direct feature to achieve.

Here's my workaround.

Sample data

vstephenmsft_1-1663824963047.png

 

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

vstephenmsft_0-1663824943499.png

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.

vstephenmsft_2-1663825084468.png

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors