PowerPivot table Multiple subtotals

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
218
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
 

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
82
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:

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
218
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.
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top