Dynamic Calculated Field Pivot Table

[alanmweb]

Hello,
I'm attempting to make a dynamic calculated field that will change based off of a number entered above the pivot table. I understand how to use the basic pivot table calculated field, but I can't figure out how to make the field dynamic without having to edit the field every time. Ideally, I want the field to be equal to - BudgetAmount / (Entered SqFtage).

Thanks,
Alan

 

[RoryA]

You could use Power Pivot for that. Turn G1:G2 into a table and load them into the data model, then add a measure that just sums the Enter Sqftage field - call it say Total_SqFt. Then load the source data into the data model and do not create any relationships to the Enter Sqftage table. You can then create a measure that is SUM(Budget)/Total_SqFt

 

[alanmweb]

You could use Power Pivot for that. Turn G1:G2 into a table and load them into the data model, then add a measure that just sums the Enter Sqftage field - call it say Total_SqFt. Then load the source data into the data model and do not create any relationships to the Enter Sqftage table. You can then create a measure that is SUM(Budget)/Total_SqFt

Hey Rory,

I haven't used power pivot before so I may be misunderstanding your response, but, why would I sum the SqFtage field? I need a calculated field which divides the budget of a cost-code by the entered number (Ex: 2700). Also, I tried using power pivot and I just got errors. Here's an example of what the raw data looks like,

 

[RoryA]

You sum it because you need an aggregation - there’s only one value so it doesn’t really matter whether you use sum/min/max/average for example.

 

[alanmweb]

You sum it because you need an aggregation - there’s only one value so it doesn’t really matter whether you use sum/min/max/average for example.

Hey Rory,

I ended up just doing (SUM(sqft))/BudgetAmount. Thank you for you direction!

-Alan