cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Group Together Certains Rows and then Take Sum of Their Columns to Get Group Subtotals

I have something similar to the following table:

 

Act#  Desc                        Budget     Actual

100    Operations             1000          800

105    Admin                      900          700

200    Chicago                  1000          500

205    St. Louis                  1000          400

210    Des Moines            1000          350

215    Detroit                    1200         400

220    Columbus               1000         250

225    Indianapolis             800          600

230    Toronto                    900          800

245    Monterrey              1200          900

300    Kingston                 1000          750

 

I would like to be able to have something like the following:

 

Act#  Desc                                Budget     Actual    

100    Operations                      1000          800

105    Admin                               900          700

          Operations Subtotal:       1900         1500

200    Chicago                           1000          500

205    St. Louis                           1000          400

210    Des Moines                     1000         350

215    Detroit                             1200         400

220    Columbus                        1000         250

225    Indianapolis                       800         600

          Midwest Subtotal:           6000        2500

230    Toronto                             900          800

245    Monterrey                       1200          900

300    Kingston                          1000          750

          International Subtotal:     3100       2450

                            Grand Total:  11000      6450

 

How would one combine the account numbers/descriptions to the appropriate groups and get the totals of each group? Appreciate the time and help. 

1 REPLY 1
Highlighted
Community Champion
Community Champion

Re: Group Together Certains Rows and then Take Sum of Their Columns to Get Group Subtotals

Create a new column in your data called "Grouping" or something similar.  Place into this column, on a row by row basis, which group each row belows.  If you already have this data, you can make use of it.

 

Once you have this additional column, you can use this column in your "Rows" data.  The matrix and table visuals will have a subtotal switch you can turn on.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors