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

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

Reply
vojtechsima
Memorable Member
Memorable Member

Matrix - Total to All when filtered

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

BUSomethingTotal by BUTotal by ItemTotal by selected BU
Elle5500706892765008608966
Louis1000154003965008608966
Total6500860896665008608966


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:

vojtechsima_0-1646739518984.png


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

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
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)

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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.