Aggregation of overlapping groupings in pivot table

Question

I would like to aggregate a value of overlapping groups in a single pivot table.

An example of my data is:

• Name, Value, Attribute 1, Attribute 2, Attribute 3
• A, 1, True, True, True
• B, 2, True, False, False
• C, 4, True, True, False

According to attribute values, the overlapping groups are:

• Attribute 1 {A, B, C}
• Attribute 2 {A, C}
• Attribute 3 {A}

So the aggregated view I want is:

• Attribute 1, 7
• Attribute 2, 5
• Attribute 3, 1

Is there a way to get this information into a single pivot table ?

It doesn't matter if the solution is in excel or Google spreadsheets.

Answer 1

In such case, I reshape the data, in order to get it in a tabular form. Having the following table structure, allow you to make the pivot you want.

Name, Value, Attribut, Attribut Value
A, 1, Attribute 1, True
A, 1, Attribute 2, True
A, 1, Attribute 3, True
B, 2, Attribute 1, True
B, 2, Attribute 2, False
B, 2, Attribute 3, False
C, 4, Attribute 1, True
C, 4, Attribute 2, True
C, 4, Attribute 3, False

To make the transformation (actually you unpivot your data as described here), you use the free Microsoft Excel add-In Power Query (from Excel 2010). In this way you can keep your data source as it is and transform it quickly.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Unpivot other columns" = Table.UnpivotOtherColumns(Source, {"Value", "Name"}, "Attribut", "Attribut Value")
in
    #"Unpivot other columns"

The pivot table is then based on the new list:

• ROWS = Attribut
• VALUES = Value
• Report Filter = Attribut value (only TRUE)

The results look like this: