Dealing with Matrix visualization

[max14]

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.

Manager	Employee	Sales	%	Istotal
Green	Green	40,000	6.90	1
Green	A	10,000	2.30	0
Green	B	10,000	4.00	0
Green	C	10,000	5.60	0
Green	D	10,000	9.8	0

Currently I am getting this utput 

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?

[AnalyticsWizard · Accepted solution]

@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]

Read about ISINSCOPE and then adjust your measure accordingly.