Variable Column Headers in or attached to a Power Pivot Table

Good afternoon,

I'd like to thank you in advance, the work here is VERY important and helpful in providing nutrition education courses.

I've created a Pivot Table using Power Pivot that tells us simple demographic information, broken down by class types for data such as gender, ethnicity, household size, etc.

However, now I need fields that can return totals based on changing headers, I'll explain. I need four headers: Variable Age, Variable Town, Variable Zip, and Variable County.

You should be able to type in the variable age for example 7 and it'll return the number of 7 year olds in each class, as well as totals on the bottom row of the pivot table. I already created functions to provide in in a previous Excel file; however, the previous excel file created a Pivot-type Table without using an actual pivot table. And with a pivot table, I'm not sure how specifically to edit a header that would effect the formula below resulting in summed demographic values that are variable based on the header. Below is the formula explained that I previously created in a non-pivot table to allow for variable age, town, zip, and county:

=SUMPRODUCT(('Data Worksheet'!$B$2:$H$999=$A1)*('Data Worksheet'!$P$2:$P$999=D$6)),"") where $A1 is the Class Name to lookup by, D$6 is the Variable Column Header. Cell Range $B$2:$H$999 is the range in which the Class Name will exist in the data worksheet, and $P$2:$P$999 is the column in which the Age, Town, Zip, or County information is contained in the class worksheet.

Again, I'm looking for the ability to move a formula such as this into a Power Pivot Table column where I can edit the headers, so when I change the header in say cell D$6 from the words "*Variable County*" to a specific county such as "Ingram County" and it will then reference "Ingram County" in the SUMPRODUCT function, looking through the data set to sum for every time a line of data contains the class data and the variable county.

I know how to create these complex formulas, I'm specifically looking for how to make the header in the power pivot table editable and have a formula that can reference this changing header. If your answer requires a different formula, please be sure to include.

If my inclination is correct, and I can't create variable pivot table headers, then a way to add columns attached or next to a pivot table that create the same effect would be greatly appreciated (it'll look very similar to what I did with the pivot-table-like formula and layout).

I understand this is complicated and you may be looking for me to link a table, but I just don't know how to link tables here and I don't have time and will be away from my computer and reallllyyyy hoping I can come back to help on this as the question is asked so please, feel free to asked for additional clarifying info, but I won't be able to provide a sample sheet.

Again, I'd like to thank whoever helps on this as it has important real life ramifications in helping those in need.

-Ryan

It is a direct connection, this file is meant so that those using it don't have to understand anything other importing new data, so I didn't want to have to have them load anything through Power Query (and I'm not experienced at all with power query yet, this is my first power pivot file!).

I'll upload a sample of the data, but it won't be a Pivot Table, just basically hypothetical demographic data.

Again, I've created the Power Pivot table to accurately sum the class demographic data, now I'm looking for the ability to create a header than I can change what is typed into it. And as a result, the formulas below (which will reference the header) will be searching for data the matches this header. So the "*Variable Age*" header could be changed by typing in the header to say, 27, and it'll lookup, based on the class type through the data to see how many 27 year olds attended that class. As I said, I've got the formula (or I can change the formula if need be), but I don't know how to create a header that I can changed in a Pivot or Power Pivot table.

Thanks.

All calculation and transformation should be done within data model or before it's loaded.

DAX formulas are efficient and will change value with context of Pivot Table filter/slicer selection.

In 99% of the case, there is no need for formula to manipulate what's been produced by PowerPivot with appropriate modeling.

Header shouldn't be used to display filter/criteria, but rather should indicate what sort of aggregation/calculation is performed, Slicer or Filter selection should indicate criteria used.

Also, note that direct connection/load to data model will severely hamper flexibility. Any change to source will likely cause issue with data model and may require complete rebuild.
I recommend that PowerQuery/Get & Transform be used as staging area for data prep and transformation prior to loading data to model.

User will not need to interact at all with PowerQuery, only the author/developer

Okay, I still have a lot to learn as I just was introduced to Power Pivot a week ago!

I'll take your suggestions in and work learning Power Query, filters, and slicers and re-ask the question once I've learned more!

Thank you, marking this as solved.