Pivot tables: columns without nesting?

%3CLINGO-SUB%20id%3D%22lingo-sub-2628016%22%20slang%3D%22en-US%22%3EPivot%20tables%3A%20columns%20without%20nesting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628016%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20dataset%20with%20a%20set%20of%20multiple%20variables%20that%20all%20have%20the%20same%20structure.%20They%20are%20questions%20with%20yes%2Fno%20answers%2C%20and%20I%20want%20to%20know%20the%20%25%20of%20cases%20that%20answered%20yes.%20I%20would%20like%20to%20summarize%20results%20for%20all%20variables%20in%20a%20single%20pivot%20table%2C%20but%20when%20I%20do%20so%20the%20variables%20are%20nested.%20So%20I%20have%20been%20resorting%20to%20making%20a%20separate%20table%20for%20every%20variable%2C%20but%20this%20is%20very%20tedious.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20file%20with%20an%20example%20of%20the%20data%20I%20am%20working%20with%20(in%20this%20case%203%20variables)%2C%20the%20pivot%20tables%20I%20have%20been%20able%20to%20make%20(separately%20for%20each%20variable)%20and%20the%20table%20structure%20I%20would%20like%20to%20have.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20any%20tips%20on%20how%20to%20do%20this!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2628016%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2628193%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3A%20columns%20without%20nesting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124092%22%20target%3D%22_blank%22%3E%40sdalle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20create%20helper%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%2097px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301816iB146FC02739ED2C1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAdd%20it%20and%20source%20table%20to%20data%20model%20and%20build%20relationships%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20548px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301817i8EC66CACC89FA1B0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAdd%203%20DAX%20measures%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3Elivestock%20index%3A%3DCALCULATE(COUNT(Table1%5B_index%5D)%2C%20USERELATIONSHIP(Answers%5BAnswer%5D%2CTable1%5Blivestock%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eanother%20two%20are%20similar.%3C%2FP%3E%0A%3CP%3EUse%20above%20measures%20building%20PivotTable%20from%20data%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20369px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301819iAC26E109F220D047%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20is%20to%20unpivot%20your%20source%20table%20using%20Power%20Query%20and%20build%20PivotTable%20on%20resulting%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2628202%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3A%20columns%20without%20nesting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124092%22%20target%3D%22_blank%22%3E%40sdalle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20expected%20your%20table%20is%20in%20a%20crosstabular%20format%20which%20already%20is%20a%20pivot%20table%20format.%3C%2FP%3E%3CP%3EYou%20have%20to%20unpivot%20your%20data%20first%20and%20then%20create%20the%20pivot%20table.%3C%2FP%3E%3CP%3EAs%20you%20also%20want%20to%20count%20the%20blanks%20you%20have%20to%20change%20them%20to%20a%20dummy%20value.%20Otherwise%20they%20get%20removed%20by%20the%20unpivot%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20I%20was%20to%20slow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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!