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
max14
Frequent Visitor

Dealing with Matrix visualization

I have a table that includes columns for Manager, Employee, Sales, and %. I'm using a matrix visual with a total row in the table. However, when I collapse the rows, Power BI performs summarization again, resulting in incorrect totals.

 

ManagerEmployeeSales%Istotal
GreenGreen40,0006.901
GreenA10,0002.300
GreenB10,0004.000
GreenC10,0005.600
GreenD10,0009.80

 

Currently I am getting this utput 

Screenshot 2024-04-06 at 10.43.45 PM.png

But the expected output is it should show 1 rows value when I collapse. Sales = 40000 and % = 6.9

 

What should I do to achive this?

 

1 ACCEPTED SOLUTION
AnalyticsWizard
Solution Supplier
Solution Supplier

@max14 

This issue can occur when Power BI performs automatic summarization on the matrix visual. When you collapse the rows, Power BI recalculates the totals, which can lead to incorrect results12.

To achieve your expected output, you can create a measure that checks if the row is a total row and ...12. Here’s an example of how you might write this measure:

Sales Measure = 
IF(
    MAX(Table1[Istotal]) = 1,
    CALCULATE(
        SUM(Table1[Sales]),
        FILTER(
            ALL(Table1),
            Table1[Manager] = MAX(Table1[Manager]) &&
            Table1[Istotal] = 1
        )
    ),
    SUM(Table1[Sales])
)

% Measure = 
IF(
    MAX(Table1[Istotal]) = 1,
    CALCULATE(
        AVERAGE(Table1[%]),
        FILTER(
            ALL(Table1),
            Table1[Manager] = MAX(Table1[Manager]) &&
            Table1[Istotal] = 1
        )
    ),
    AVERAGE(Table1[%])
)

In these measures, Table1 is the name of your table. These measures check if the current row is a total row (Istotal = 1). If it is, they calculate the sum or average across all total rows for the current manager. If it’s not a total row, they simply return the sales or % value for the current row12.

Please replace Table1 with your actual table name. If you need further assistance or have more specific requirements, feel free to ask! 😊

View solution in original post

2 REPLIES 2
AnalyticsWizard
Solution Supplier
Solution Supplier

@max14 

This issue can occur when Power BI performs automatic summarization on the matrix visual. When you collapse the rows, Power BI recalculates the totals, which can lead to incorrect results12.

To achieve your expected output, you can create a measure that checks if the row is a total row and ...12. Here’s an example of how you might write this measure:

Sales Measure = 
IF(
    MAX(Table1[Istotal]) = 1,
    CALCULATE(
        SUM(Table1[Sales]),
        FILTER(
            ALL(Table1),
            Table1[Manager] = MAX(Table1[Manager]) &&
            Table1[Istotal] = 1
        )
    ),
    SUM(Table1[Sales])
)

% Measure = 
IF(
    MAX(Table1[Istotal]) = 1,
    CALCULATE(
        AVERAGE(Table1[%]),
        FILTER(
            ALL(Table1),
            Table1[Manager] = MAX(Table1[Manager]) &&
            Table1[Istotal] = 1
        )
    ),
    AVERAGE(Table1[%])
)

In these measures, Table1 is the name of your table. These measures check if the current row is a total row (Istotal = 1). If it is, they calculate the sum or average across all total rows for the current manager. If it’s not a total row, they simply return the sales or % value for the current row12.

Please replace Table1 with your actual table name. If you need further assistance or have more specific requirements, feel free to ask! 😊

lbendlin
Super User
Super User

Read about ISINSCOPE and then adjust your measure accordingly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.