Sep 10 2020 03:16 AM - edited Sep 10 2020 03:41 AM
Hi all, I have attached workbook. I need to summarize the data which is like below
Branch | Annexure I | Annexure II |
AHMED NAGAR | No | No |
AHMEDABAD | Yes | No |
into following
Type | Yes | No |
Annexure I | Count | Count |
Annexure II | Count | Count |
Annexure III | Count | Count |
Annexure IV to XI | Count | Count |
Annexure X | Count | Count |
Annexure XI | Count | Count |
Annexure XII | Count | Count |
without formulae so that I can drill through the data when I click on count values to further investigate.
Whether it is possible in Pivot Table or Power Pivot or BI where I can have drill through or some other ways of data consolidation and not formula like sumif or countif. Advanced Filter will also be okay.
Sep 10 2020 04:15 AM
Solution
There are many way to handle such kind of data, below two are my favorite
Solution 01 - Formula Solution Array CSE
{=SUMPRODUCT((Table1[[#Headers],[Annexure I]:[Annexure XII]]=$K2)*(Table1[[Annexure I]:[Annexure XII]]=L$1))}
You can use the above formula
If you Microsoft 365 then you just need to press enter
Solution 02 - Power Query Solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Branch"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "Yes", "No"})
in
#"Reordered Columns"
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Sep 10 2020 09:51 PM
@Faraz Shaikh Thanks for the solution.
I found power query solution better and modified to use only till step 2 so that i can have drill through to see the branches.
Sep 10 2020 04:15 AM
Solution
There are many way to handle such kind of data, below two are my favorite
Solution 01 - Formula Solution Array CSE
{=SUMPRODUCT((Table1[[#Headers],[Annexure I]:[Annexure XII]]=$K2)*(Table1[[Annexure I]:[Annexure XII]]=L$1))}
You can use the above formula
If you Microsoft 365 then you just need to press enter
Solution 02 - Power Query Solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Branch"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "Yes", "No"})
in
#"Reordered Columns"
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more