PowerPivot table Multiple subtotals

shophoney

Active Member
Joined
Jun 16, 2014
Messages
281
Hi, I'm wondering is it possible to have a power pivot report table that has the details of units sold by one criteria. Then also shows multiple subtotals at the end.

IE: Total units sold by Brand is the report. Then I have a subtotals at the bottom listing the season. Another subtotals listing all the brands. And yet another subtotal listing all the CLASSES.

I can copy and paste the report to make 4 different reports listing each criteria. But I never know the length of the port and it might overlap or have huge space.

I also don't want to modify the report each time for formatting it 4 different ways.

Thanks
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
If you have a 4 column table named Table1 with header row of Units,Brand,Class,Count then in powerquery this produces multiple report totals at bottom
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Total1 = Table.Group(Source, {"Units"}, {{"Count", each List.Sum([Count]), type number}}),
    Total2 = Table.Group(Source, {"Brand"}, {{"Count", each List.Sum([Count]), type number}}),
    Total3 = Table.Group(Source, {"Class"}, {{"Count", each List.Sum([Count]), type number}}),
    Line= Table.InsertRows(Source,Table.RowCount(Source),{[Brand = "----", Units="----", Class="----", Count="----"]}),
    Combined = Line & Total1 & Total2 & Total3
in Combined
 
Last edited:
Upvote 0
If you have a 4 column table named Table1 with header row of Units,Brand,Class,Count then in powerquery this produces multiple report totals at bottom
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Total1 = Table.Group(Source, {"Units"}, {{"Count", each List.Sum([Count]), type number}}),
    Total2 = Table.Group(Source, {"Brand"}, {{"Count", each List.Sum([Count]), type number}}),
    Total3 = Table.Group(Source, {"Class"}, {{"Count", each List.Sum([Count]), type number}}),
    Line= Table.InsertRows(Source,Table.RowCount(Source),{[Brand = "----", Units="----", Class="----", Count="----"]}),
    Combined = Line & Total1 & Total2 & Total3
in Combined


Hi, I'm not sure I follow how to do this. My data comes from an SQL connection to our server DB.

I have built many powerpivot reports but need to add subtotals to the report.

So I have total units sold by CLASS, and it lists all the related classes.
Then subtotal by brand and it lists all the brands.
Then again a subtotal listing all the units sold by season.

So my totals will all be the same. But we can slice and dice the view showing the data by any type.

Otherwise I have to build the same report several times. and the print different ranges of data. For each subtotal area. In access you would use report footers. As Excel data can be different lengths I can't even predefined the range.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top