I've got a flat file of data for property occupancy numbers that I need to put into a pivot table. The occupancy is provided, but I have groupings that end up just summing the Occupancy %'s:


The 2163% is the problem (bunch of hidden rows for simplicity).

The easy answer is a calculated field, but since I'm pulling from a flat file, I dropped Field into the Columns to show all the data. In the Calculated Field box, only "Field" is shown as a calculation source. Is there a way around this? I just need my formula to be =1 - (Vacancies / Available)

thanks

Not totally sure I understand....but it sounds like the problem is with how your source data is set up.

You probably have the "Field" attributes listed down a single column and then one column for values.



What you need is a column for each Field type (like how you have your pivot currently set up).



That will allow you to treat each field as an independent metric which you can then calculate the occupancy off of.

If changing the layout of the source data is not an option, you'll probably just have to use a formula to calculate it next to the pivot table.

Ok that's what I was afraid of. I was hoping to keep it in the flat file format, but I can change it to a crosstab reasonably easily.

Thanks for the input.

I'm not pro on this at all, but I'm pretty sure you can use power query to unpivot it fairly easily.