cancel
Showing results 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

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.

 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?

1 ACCEPTED SOLUTION
Solution Supplier

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! 😊

2 REPLIES 2
Solution Supplier

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! 😊

Super User

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors