Pivot tables: columns without nesting?

New Contributor


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


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.


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.



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.



One more way is with dynamic arrays

  total, COUNTA(Table1[_index]),
    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!