Pivot tables reading binary data

[hsouth11]

I am creating summary statistics for a research projects on wetlands. The dataset has data for 2301 wetlands. A great deal of that data is binary. For example, each wetland is classified as being marine: 0 = no, 1 = yes. I want to create a pivot table that displays what percentage of wetlands are marine/inland etc. but I haven't figured out which 'display data as' option to use. If anyone has any suggestions I would be most appreciative!

Thanks.
Hanno

[sandy666]

maybe use percent of total

[hsouth11]

Here is the problem with that. When I put the one of the binary variables in the values thread and use the count function I get the total number of sites and not the total number of sites belonging to a particular criteria (because I can't use the COUNTIF function). So then if I use % of total, I just get 100%.

[sandy666]

Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[hsouth11]

Okay, I have attached a sample workbook. I have included two types of columns: (1) site name which identifies each wetland
and criteria which are binary data for each wetland. My goal is to create pivot table that shoes what percent of wetlands
fulfill each criteria and then to be able to add to that table so that the figures are displayed in groups based on region,
country (I haven't included this data) etc.

Thanks for your help!

[sandy666]

I did what I can with your example
Is that what you want?

[hsouth11]

Thanks for taking the time to make this up. I think it is close and I might be able to work backward from what you did but I can't open the pivot table. I get the error: "This PivotTable connects to an external data source that's not supported in this version of Excel." Is this something you can fix on your end?

[sandy666]

Are you on Mac?

[hsouth11]

Yes I am on a mac.

[sandy666]

Mac Excel doesn't support PowerQuery so I can't help you.
Maybe someone else will give you proper solution to do this on Mac

Have a nice day

btw. could you add three letters to the MS-Off version in your profile: Mac ?

[sandy666]

but maybe this one (without PowerQuery but with additional column in source data)

edit:
I forgot to delete previous result so ignore it