Forum Discussion
Pivot tables: columns without nesting?
Hi,
I have a dataset with a set of multiple variables that all have the same structure. They are questions with yes/no answers, and I want to know the % of cases that answered yes. I would like to summarize results for all variables in a single pivot table, but when I do so the variables are nested. So I have been resorting to making a separate table for every variable, but this is very tedious.
I have attached a file with an example of the data I am working with (in this case 3 variables), the pivot tables I have been able to make (separately for each variable) and the table structure I would like to have.
I would appreciate any tips on how to do this!
5 Replies
One more way is with dynamic arrays
=LET( total, COUNTA(Table1[_index]), CHOOSE({1,2,3}, COUNTIFS(Table1[crop], "="&G37:G39)/total, COUNTIFS(Table1[livestock], "="&G37:G39)/total, COUNTIFS(Table1[flood], "="&G37:G39)/total) )Please check attached.
- @Segei Baklan, Detlef_Lewin, thank you so much for your responses. I managed to figure out the power query approach after studying the files you sent and reading about unpivoting columns. It is very straightforward, now that I understand. There is so much to learn about Excel - thank you!
In attached is Power Query variant - replace on empty cells (null) on text ("blank"), unpivot other than index columns and return result as PivotTable into the sheet.
As expected your table is in a crosstabular format which already is a pivot table format.
You have to unpivot your data first and then create the pivot table.
As you also want to count the blanks you have to change them to a dummy value. Otherwise they get removed by the unpivot process.
Edit: I was to slow.
You may create helper table
Add it and source table to data model and build relationships
Add 3 DAX measures as
livestock index:=CALCULATE(COUNT(Table1[_index]), USERELATIONSHIP(Answers[Answer],Table1[livestock]))another two are similar.
Use above measures building PivotTable from data model
Another way is to unpivot your source table using Power Query and build PivotTable on resulting table.
