cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Memorable Member

## Matrix - Total to All when filtered

Hi, guys,
I am quite struggling with this one:
I need this table:

 BU Something Total by BU Total by Item Total by selected BU Elle 5500 7068927 6500 8608966 Louis 1000 1540039 6500 8608966 Total 6500 8608966 6500 8608966

I need to get the Total of "Total by BU" to Total by selected BU.
I am able to retrieve the Total By BU using this measure:

``````var __SelectedBUs = VALUES('sample'[BU])

var __calc = CALCULATE(SUM('sample'[Something]),ALL('sample'[Item],'sample'[BU]), 'sample'[BU] IN __SelectedBUs)``````

However, I can't figure out how to put the total to All rows in the next column.
There's an active "Item" slicer:

What it should do is that it takes all "BUs" that is filtered by "Item", then toss the "Item" filter out, SUM(Something) of all the filtered BUs together and return it back to all rows. Similar to "Total By BU" but return the total to all rows.

1 ACCEPTED SOLUTION
Memorable Member

Hi, @amitchandak
I am a bit embarrassed that I didn'T figure this out, however, I managed to fix it by referencing the previous measure and adding the ALL filter.

``````var __calc = CALCULATE('sample'[AllexceptBU], ALL('sample'[BU]))
return __calc``````

However, I appreciate your time and effort. Thank you

3 REPLIES 3
Super User

@vojtechsima , try if one of the two can help

var __SelectedBUs = VALUES('sample'[BU])

var __calc = CALCULATE(SUM('sample'[Something]),Keepfilters('sample'[BU] IN __SelectedBUs)

or

var __SelectedBUs = VALUES('sample'[BU])

var __calc = CALCULATE(SUM('sample'[Something]),'sample'[BU] IN __SelectedBUs)

Memorable Member

Hi, @amitchandak
I am a bit embarrassed that I didn'T figure this out, however, I managed to fix it by referencing the previous measure and adding the ALL filter.

``````var __calc = CALCULATE('sample'[AllexceptBU], ALL('sample'[BU]))
return __calc``````

However, I appreciate your time and effort. Thank you

Memorable Member

Hi, @amitchandak
Unfortunately, this gives me something that I already did in previous tries, I am still not getting the same result for all rows in my matrix.
I need to have the sum of mentioned BUs in all rows (the same value) and I also need to filter out the "Item" slicer.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors