Aug 09 2021 08:47 AM
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!
Aug 09 2021 09:18 AM
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.
Aug 09 2021 09:25 AM - edited Aug 09 2021 09:26 AM
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.
Aug 09 2021 09:26 AM
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.
Aug 09 2021 09:49 AM
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.
Aug 10 2021 07:18 AM