Pivot tables: columns without nesting?

New Contributor

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

@sdalle 

You may create helper table

image.png

Add it and source table to data model and build relationships

image.png

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

image.png

 

Another way is to unpivot your source table using Power Query and build PivotTable on resulting table.

@sdalle 

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.

 

@sdalle 

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.

image.png

@sdalle 

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!