Pivot Table - Sum of values from a column

[mian.tx.00]

Hello experts - I have an excel sheet (see attached). I need to create a pivot report/table. Where I need to report count of different categories( column C - there are 4 categories). The sum for each category should be calculated based on values from Column D for each unique ITEM ID (column A) .

Example:
Item ID 20 has 10 rows, associated with COB, we will pick 6 from column D,
Item ID 21 has 17 rows, associated with COB, we will pick 6 from Column D
Item ID 23 has 12 rows, associated with COHP, we will pick 3 from column D
Item ID 25 has 15 rows, associated with CIT, we will pick 6 from column D
Item ID 26 has 35 rows, associated with TC, we will pick 9 from column D
Item ID 28 has 8 rows, associated with TC, we will pick 2 from Column D

Pivot table should display report as

COB 12 ----- 6 +6
COHP 3
CIT 6
TC 11 ------- 9 +2
Grand Total: 32

 

[Nebu]

Hi:

Find the attached. I have used Power pivot and DAX to achieve this.


Thanks

 

[mian.tx.00]

Hi:

Find the attached. I have used Power pivot and DAX to achieve this.


Thanks

Perfect! This is exactly What i was looking for. Thank You!

 

[GraH - Guido]

Just for fun adding an alternative with Power Query

- Loading the table into PQ
- using group by 2x
- Using Table.InsertRows() to add the grand total line with a List.Sum().

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}, {"VersionNumber", Int64.Type}, {"Category", type text}, {"Applied", Int64.Type}}),
    GroupBy = Table.Group(#"Changed Type", {"Item ID", "Category"}, {{"Count", each List.Min([Applied]), type number}}),
    PivTable = Table.Group(GroupBy, {"Category"}, {{"Count", each List.Sum([Count]), type number}}),
    AddGrandTotal = Table.InsertRows(PivTable,Table.RowCount(PivTable),{[Category = "Grand Total", Count = List.Sum(Table.Column(PivTable,"Count"))]})
in
    AddGrandTotal

Alternative to the last step to add grand total is simply using the table total row of the Power Query result table. And thus possible with some simple mouse clicks.

 

[bosco_yip]

Formula solution.



In J4, copied down :

=SUMPRODUCT(($C$2:$C$98=H4)*(MATCH($A$2:$A$98,$A$2:$A$98,0)=ROW($A$2:$A$98)-ROW($A$1)),$D$2:$D$98)

Regards
Bosco