CODEWITHSUNDEEP
excelpivotaveragecalculated-field
blhdev
blhdev

Reputation: 1

Excel pivot table average sum of amount based on unique sq ft

I have a raw data table that populates dynamically into my excel workbook and has period, amount, expense type, Square Footage, property category

I need to get a pivot table column that will allow me to calculate the average amount per SF for all the periods (or once I add slicers whatever is selected with regard to periods).

My issue is that the Square Footage is a repeating unique value. So I really need the total amount for the expense type for all the periods by the property to be divided by the unique SF value for the property not the sum of the square footage.

So my rows are: Property Category Property Expense Type

I need my column to be the calculated Total of Amount/Unique SF value for the property and the grand total would be the Total of Amount/Sum of Unique SF values for each property.

I don't know how to get the equivalent of "max" in a calculated field to carve out the individual sf values.

Thanks in advance for your help.

Upvotes: 0

Views: 55

Answers (1)

blhdev
blhdev

Reputation: 1

Apparently, I just needed to post here to get inspiration. I added a helper column with 1 in each row.

Created a calculated field with Amount/(SF/Helper)

Checked it manually and it is good. Let me know if you think I missed something.

Upvotes: 0

Related Questions