PowerPivot table Multiple subtotals

[shophoney]

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]

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

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

 

[shophoney]

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

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.