Forum Discussion
Summary under subtotal
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
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
13 Replies
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.Sorry, Pallavi, I don't see your previous message. And I haven't got a solution yet.
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
Thanks, Akash_Varuna.
The problem with using Power Query is that it is difficult to insert the subtotals of all years to each faculty.
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.
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.
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.
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!
Hi carsoncheng pls check this , here the ROLLUP function adds subtotals for each Year and Total for all product categories in the table.
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_TotalThanks 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!
You're welcome 😉
