SOLVED

Subtotals after each group

%3CLINGO-SUB%20id%3D%22lingo-sub-1994112%22%20slang%3D%22en-US%22%3ESubtotals%20after%20each%20group%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994112%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20this%20community%20and%20I%20haven't%20really%20worked%20with%20excel%20in%20terms%20of%20creating%20reports.%20I%20have%20been%20using%20Power%20BI%20but%20i%20am%20not%20able%20to%20recreate%20the%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20below%20report%20in%20Business%20Objects%20and%20would%20like%20to%20replicate%20this%20in%20excel.%20In%20yellow%20is%20what%20i%20would%20like%20to%20show.%20So%20the%20grouping%20is%20based%20on%20W%20litres%20and%20then%20the%20collection%20litres%20are%20summed.%20Is%20this%20possible%20to%20do%20in%20excel%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22GcZ0T.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241079iD4581C4C768D2A36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22GcZ0T.jpg%22%20alt%3D%22GcZ0T.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1994112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994180%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20after%20each%20group%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903525%22%20target%3D%22_blank%22%3E%40YasirAman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20anywhere%20in%20the%20table.%3C%2FP%3E%0A%3CP%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20in%20the%20Outline%20group%2C%20click%20Subtotal.%3C%2FP%3E%0A%3CP%3ESelect%20W_LITRES%20in%20the%20'At%20each%20change%20in%3A'%20dropdown.%3C%2FP%3E%0A%3CP%3ETick%20the%20check%20box%20for%20COLLECTION_LITRES%20in%20the%20'Add%20totals%20to%3B'%20dropdown%2C%20and%20clear%20the%20check%20boxes%20for%20columns%20you%20don't%20want%20to%20total.%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2009318%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20after%20each%20group%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthanks%20for%20that!%20Is%20there%20anyway%20to%20have%20the%20subtotals%20on%20the%20same%20line%3F%20I%20want%20to%20have%20max%20of%20two%20columns%20then%20a%20sum%20of%20another%20column%2C%20but%20it%20is%20creating%20two%20separate%20lines%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2009327%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20after%20each%20group%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903525%22%20target%3D%22_blank%22%3E%40YasirAman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20explain%20in%20more%20detail%20what%20you%20want%20to%20do%2C%20perhaps%20attach%20a%20sample%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2009352%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20after%20each%20group%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Battached%20is%20what%20i%20have%20as%20an%20example.%20In%20Yellow%2C%20this%20is%20what%20the%20subtotals%20is%20producing%2C%20in%20Green%20is%20what%20i%20would%20like%20it%20to%20show.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all, 

 

I am new to this community and I haven't really worked with excel in terms of creating reports. I have been using Power BI but i am not able to recreate the below. 

 

I have the below report in Business Objects and would like to replicate this in excel. In yellow is what i would like to show. So the grouping is based on W litres and then the collection litres are summed. Is this possible to do in excel?GcZ0T.jpg

6 Replies

@YasirAman 

Click anywhere in the table.

On the Data tab of the ribbon, in the Outline group, click Subtotal.

Select W_LITRES in the 'At each change in:' dropdown.

Tick the check box for COLLECTION_LITRES in the 'Add totals to;' dropdown, and clear the check boxes for columns you don't want to total.

Click OK.

@Hans Vogelaar thanks for that! Is there anyway to have the subtotals on the same line? I want to have max of two columns then a sum of another column, but it is creating two separate lines?

@YasirAman 

Can you explain in more detail what you want to do, perhaps attach a sample workbook?

@Hans Vogelaar attached is what i have as an example. In Yellow, this is what the subtotals is producing, in Green is what i would like it to show. 

Best Response confirmed by YasirAman (New Contributor)
Solution

@YasirAman 

You can get subtotals on one line by creating them in one go, then altering the formulas.

But it won't work for the DATETIME_CUST column since it contains text values that Excel doesn't recognize as valid dates and times. It will return 0 as maximum. You'd have to change the values to real date/time values for the SUBTOTAL formulas to work.

See the attached version.

Thanks!