Sum MAX values in pivot table

[Cwillson]

Hiya,

I have created a pivot table from the sample data below, which has repeating Revenue rows due to more than one Child ID being associated with a Parent ID.

I need to be able to SUM the Parent ID Revenue in this pivot table, but only for the unique values for each Parent ID, and not the duplicated values.

Any thoughts please, oh great hive mind?

Is this something to do with adding a calculated field, or is there a more out of the box solution?

N.B. the repeated values for Parent Revenue are required for some other calculations I'm doing within the workbook, so these cannot be removed from the source data.

Many thanks in advance

Chris

Parent ID	Child ID	Parent Revenue
43​	3470576​	£ 118,015.46
43​	3469783​	£ 118,015.46
43​	3483279​	£ 118,015.46
43​	3470503​	£ 118,015.46
43​	3474825​	£ 118,015.46
43​	3476412​	£ 118,015.46
43​	3483150​	£ 118,015.46
6729​	3327114​	£ 59,640.34
6729​	3470578​	£ 59,640.34
6729​	3344921​	£ 59,640.34
6729​	3396692​	£ 59,640.34
6729​	3323068​	£ 59,640.34
6729​	3476221​	£ 59,640.34
6729​	3274068​	£ 59,640.34
6729​	3418275​	£ 59,640.34
10306​	2661863​	£ 34,229.00
77458​	3264433​	£ 18,287.70

 

[MrExcel]

Hello Chris
Thanks for posting a sample of your data. Right now, in the sample shown, every row for Parent ID = 43 has a value of $118,015.46. Are you saying that there might be other rows further down where you might have a parent ID of 43 and a different value such as 19,123.45? And in that case, you would want to show Parent ID 43 with a total of 118015.46+19123.45?

My solution is to build your pivot table using the Get & Transform Tools on the Data tab instead of the Pivot Table icon on the Insert tab. The advantage of Get & Transform is that you can have Excel do a pre-step in memory of removing the duplicates before you Group by Parent ID and Sum Parent Revenue.

If you have never used the Get & Transform tools, it might be intimidating. I've created a two-minute video showing you how it works:

But, if you are a pro at Get & Transform, then here is the M code to make it happen:

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent ID", type text}, {"Child ID", type text}, {"Parent Revenue", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Child ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Parent ID"}, {{"Total Revenue", each List.Sum([Parent Revenue]), type nullable number}})
in
    #"Grouped Rows"

 

[Cwillson]

Hello Chris
Thanks for posting a sample of your data. Right now, in the sample shown, every row for Parent ID = 43 has a value of $118,015.46. Are you saying that there might be other rows further down where you might have a parent ID of 43 and a different value such as 19,123.45? And in that case, you would want to show Parent ID 43 with a total of 118015.46+19123.45?

My solution is to build your pivot table using the Get & Transform Tools on the Data tab instead of the Pivot Table icon on the Insert tab. The advantage of Get & Transform is that you can have Excel do a pre-step in memory of removing the duplicates before you Group by Parent ID and Sum Parent Revenue.

If you have never used the Get & Transform tools, it might be intimidating. I've created a two-minute video showing you how it works:

But, if you are a pro at Get & Transform, then here is the M code to make it happen:

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent ID", type text}, {"Child ID", type text}, {"Parent Revenue", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Child ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Parent ID"}, {{"Total Revenue", each List.Sum([Parent Revenue]), type nullable number}})
in
    #"Grouped Rows"

THIS IS AMAZING!! Thank you so so much. Totally opened my mind to a whole new world of possibilities. Why I've never used G&T before, I'll never know. Thought I was pretty proficient at excel (but not VBA!).

And the video instruction too - I bow down! lol

Thanks again