CODEWITHSUNDEEP
pivot-tablepowerqueryexcel-2016
GoldBishop
GoldBishop

Reputation: 2861

Sum Category but Max Sub-Items

Scenario

I have a scenario where I will have multiple entries in a Table and need a way to report on this by LineItem.

Currently the only real option I can see is a Pivot Table: - Rows: ColumnA, ColumnC - Values: Max(ColumnD)

Table (Data)

ColumnA ColumnB     ColumnC     ColumnD ColumnE ColumnF
34      8/20/2019   LineItem1   490     0       2541
34      8/21/2019   LineItem1   412     0       2614
34      8/22/2019   LineItem1   383     0       2648
34      8/20/2019   LineItem2   560     0       504
34      8/21/2019   LineItem2   588     0       507
34      8/22/2019   LineItem2   612     0       507

Pivot (Current)

Row Labels      Max of ColumnD
34              612
    LineItem1   490
    LineItem2   612

Pivot (Desired effect)

Row Labels      Sum of ColumnD
34              1102
    LineItem1   490
    LineItem2   612

"Sum of ColumnD" is to be the Sum of the LineItems below, not the data-set

Question

Not sure if this is possible with the Pivot; if not, then please advise another approach within Excel 2016 that does not include manual copy paste scenarios of getting the data out.

Upvotes: 0

Views: 294

Answers (2)

Terry W
Terry W

Reputation: 3257

PIVOT TABLE APPROACH

Pivot Table has a function called Calculated Item which can achieve what you're after.

See below screen-shot for the steps. The downside is that you need to manually add up all LineItems in each category which could be time-consuming if you have a large data-set.

You can choose to show the subtotal on top and use conditional formatting to 'hide' the calculated line item, or you can choose to hide the subtotal on top and show the calculated line item only (as in my example). You can use another name for the calculated line item so once you sort the column ascending/descending it is the first one on top.

Calculated Item

POWER QUERY APPROACH

Use the From Table function (under Data tab) to load the source data to Power Query Editor;

Use the Group By function (under Transform tab of the editor) to group the data to show the maximum value from each LineItem under each category;

Close & Load the result, create connection only for the query and add it to the Data Model;

Insert a new pivot table using data stored in the Data Model and show the total for the Max field.

See below steps for more clarifications.

Power Query Approach

The logic is similar to the answer of creating a pivot table to show the MAX and then create another pivot table to show the SUM of the MAX. However you can do more complicated calculations in Power Query, and it is suitable for a large data-set environment.

Upvotes: 1

CR7SMS
CR7SMS

Reputation: 2584

One way to do this is to create another Pivot table using the output of the first pivot table.

Note: You have to copy and paste the output of the first table before creating the next pivot table

First Pivot table Output (Remove Subtotals, Use Tabular format, Repeat Item Labels):

ColumnA ColumnC max of ColumnD
34      LineItem1      490
34      LineItem2      612

Create another pivot table using the output of the first pivot, putting ColumnA and ColumnC on rows and Sum(max of columnD) in values:

ColumnA ColumnC Sum of max of ColumnD
34      LineItem1      490
34      LineItem2      612
34 Total               1102

Upvotes: 0

Related Questions