Summary under subtotal

[carsoncheng]

Hi,

(Sorry if I post this to the wrong forum but I couldn't find a proper one.)

I have these columns in my (simplified) data table in Excel:

Faculty	Year	Program code	Quota
A	1	B00001	10
A	1	B00002	20
A	2	B00003	25
A	3	M00001	30
B	1	B00001	15
B	2	B00002	20

I would like to generate a table of by-year totals of Quota under the records of each faculty:

Faculty	Year	Program code	Quota
A	1	B00001	10
A	1	B00002	20
A	2	B00003	25
A	3	M00001	30
By-year	1		30
	2		25
	3		30
	Total		85
B	1	B00001	15
B	2	B00002	20
By-year	1		15
	2		20
	3		0
	Total		35
All	1		45
	2		45
	3		30
	Total		120

Subtotal of pivot table does not work as a subtotal is put under each Year.  I'm familar with Power Query but I think it is not how it works.

I'm wondering if Power Pivot can do the work as it seems GROUPBY() or SUMMERIZECOLUMNS() is what I need.  But I'm not familar with DAX.

Please shed some light on me!  Oh, I'd like to stick with Excel. 

Thanks!! 

Regards,

Carson

[dufoq3 · Accepted solution]

Hi @carsoncheng, it is not ideal but possible also in PowerQuery:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYicDIAAxDA2UYnXQxI2ADCOEuBFM3BjEMYWLg7i+MHOMIeqdMMw3hYsboZsfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Faculty = _t, Year = _t, #"Program code" = _t, Quota = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Quota", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Faculty"}, {{"T", each 
        [ A = _,
        //   A = GroupedRows{[Faculty="A"]}[T],
          SelectedCols = Table.Buffer(Table.SelectColumns(A,{"Year", "Quota"})),
          Ad_Totals = List.Accumulate(let lst = List.Distinct(SelectedCols[Year]) in lst & {lst} , A, (s,c)=>
                if c is number then Table.InsertRows(s, Table.RowCount(s), { SelectedCols{0} & [Faculty=if c = SelectedCols{0}[Year] then "By-year" else null, Year=c, Program code=null, Quota=List.Sum(Table.SelectRows(SelectedCols, each [Year] = c)[Quota])] })
                else Table.InsertRows(s, Table.RowCount(s), { SelectedCols{0} & [Faculty=null, Year="Total", Program code=null, Quota=List.Sum(Table.SelectRows(SelectedCols, each List.Contains(c, [Year]))[Quota])] })),
          Ad_SpaceRow = Table.InsertRows(Ad_Totals, Table.RowCount(Ad_Totals), { List.Accumulate(Table.ColumnNames(Ad_Totals), [], (s,c)=> Record.AddField(s, c, null)) } )
        ][Ad_SpaceRow], type table}}),
    CombinedT = Table.Combine(GroupedRows[T]),
    SelectedCols2 = Table.Buffer(Table.SelectRows(CombinedT, each [Program code] = null and [Year] <> null)[[Year], [Quota]]),
    Ad_Total = List.Accumulate(List.RemoveNulls(List.Distinct(SelectedCols2[Year])), CombinedT, (s,c)=>
                Table.InsertRows(s, Table.RowCount(s), { SelectedCols2{0} & [Faculty=if c = SelectedCols2{0}[Year] then "All" else null, Year=c, Program code=null, Quota=List.Sum(Table.SelectRows(SelectedCols2, each [Year] = c)[Quota])] }))

in
    Ad_Total

[carsoncheng]

Thanks a ton, @dufoq3 !!  Your answer is eye-opening.  I thought I was familar with Power Query. Thanks again for showing me the real power of Power Query!

[dufoq3]

You're welcome 😉

[v-pagayam-msft]

Hi @carsoncheng ,
Thank you for connecting us on Microsoft Fabric Community Forum!

Thank you for the prompt response @Poojara_D12  and @Akash_Varuna , it helps to address the query.

I wanted to check and see if you had a chance to review our previous message or Please let us know if everything is sorted or if you need any further assistance.If our response addressed your query, please mark it as Accept as Solution and click Yes if you found it helpful.

Regards,
Pallavi.

[carsoncheng]

Sorry, Pallavi, I don't see your previous message. And I haven't got a solution yet.

[techies]

Hi @carsoncheng have you tried using rollup function in dax? If you'd like, I can share a reference or example to help you get started.

[carsoncheng]

No, I haven't.  I'm not familar with DAX functions so it will be helpful if you can share an example. Thanks a lot!

[techies]

Hi @carsoncheng pls check this , here the ROLLUP function adds subtotals for each Year and Total for all product categories in the table.

[carsoncheng]

Thanks for the effort but it somehow does not work on my side. I guess DAX in Excel may be a bit different from that in Power BI.

(The field names are a bit different.)

Anyway, thanks!

[Poojara_D12]

Hi @carsoncheng 

To achieve your desired table structure in Excel, Power Pivot with DAX is the best approach. First, you need to load your data into Power Pivot by adding it to the Data Model. Once inside Power Pivot, create a measure named TotalQuota using SUM(Data[Quota]) to sum the Quota column. Then, insert a Pivot Table from Power Pivot and place Faculty and Year in the Rows section while placing TotalQuota in the Values section. To generate by-year totals within each faculty, define another measure called ByYearQuota using IF(HASONEVALUE(Data[Year]), [TotalQuota], CALCULATE([TotalQuota], ALLEXCEPT(Data, Data[Year]))), ensuring that subtotals correctly roll up within each faculty while maintaining individual year-wise quotas. Additionally, to compute the total quota across all faculties, define another measure, GrandTotalQuota = CALCULATE([TotalQuota], ALL(Data[Faculty])), ensuring an overall summary at the bottom. Finally, format the Pivot Table by enabling Subtotals at the bottom and Grand Totals for both rows and columns under the Pivot Table Design options. This setup allows Power Pivot to display faculty-specific quotas, year-wise subtotals, and grand totals in the correct format, overcoming the limitations of default Pivot Table subtotals.

[carsoncheng]

Thanks a lot, Poojara!

I added the three measures successfully, but I have no idea how I can insert the subtotals to the end of each Faculty. I'd like to stress that I'm using Power Pivot in Excel, not Power BI.

I would appreciate if you could explain a bit more.

[Akash_Varuna]

Hi @carsoncheng , Could you try this please , After Loading the data please try these out in Power Query

• Create Subtotals and Totals:

  • By-Year Subtotals: Duplicate the query → Group by Faculty and Year → Sum Quota → Add a column for "By-year."
  • Faculty Totals: Duplicate again → Group by Faculty → Sum Quota → Add a "Total" column.
  • Grand Total: Group the entire table (no grouping fields) → Sum Quota → Label as "All."

• Combine Tables: Append the original data with these summaries.
  If this post helped please do give a kudos and accept this as a solution 
  Thanks In Advance

[carsoncheng]

Thanks, @Akash_Varuna. 

The problem with using Power Query is that it is difficult to insert the subtotals of all years to each faculty.