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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Subtotal Aggregations

Hi,

 

I have a dataset with the following tables, among others: Month, Business Unit, Product Line, and Pounds Sold. I'm trying to create at table where the pounds are aggregated across a product line, but ignoring the Business Unit context, and keeping the Month context. A sample table would like like the following:

                              Business Unit A       Business Unit B        Business Unit C         Total

Product Line 1         54,000                     54,000                        54,000                    54,000

Product Line 2        120,000                    120,000                      120,000                  120,000

Product Line 3         78,000                      78,000                      78,000                     78,000

 

I would have a month slicer off to the side that would change the values in this table accordingly. I've tried several combinations of CALCULATE, FILTER, ALL, and ALLSELECTED to get a table like this, but no luck. Any help would be greatly appreciated!

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

If Business Unit is in it's own table that has a 1-to-many relationship to Pounds Sold, this should be as simple as the following:

 

CALCULATE( SUM( 'Pounds Sold'[Pounds Sold] ), ALL('Business Unit') )

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

If Business Unit is in it's own table that has a 1-to-many relationship to Pounds Sold, this should be as simple as the following:

 

CALCULATE( SUM( 'Pounds Sold'[Pounds Sold] ), ALL('Business Unit') )

Anonymous
Not applicable

Yup, that's all it was, I just wasn't thinking about it clearly. Thanks for the help!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.