Dynamic Calculated Field Pivot Table

alanmweb

New Member
Joined
Jul 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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).

mr_excel.PNG


Thanks,
Alan
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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,
mrexcel.PNG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

alanmweb

New Member
Joined
Jul 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,159
Messages
5,745,116
Members
423,925
Latest member
globaltlg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top