1

I am new to PowerPivot within Excel, but am familiar with SQL. I import a table from my SQL server into PowerPivot, containing the results of some surveys; the columns are: City, State, Population, avg income, avg income x population.

I need to create a Pivot Table showing the weighted average income by State. If the source of the data were an Excel sheet, I'd add a calculated field to the pivot table, calculating [avg income x population] / [Population].

I cannot seem to that in my case, since the source is a PowerPivot data source.

Suggestions?

I understand I can add calculated fields and measures to the PowerPivot table, but that's not really what I am after.

I am sure it's because my google skills are subpar, but I have also spent lots of time searching for solutions, to no avail.

E.g. these discussions show how to add a field to the powerpivot source, but it's not clear to me how you add a calculated field to a Pivot table which reads from PowerPivot.

PowerPivot formula for row wise weighted average

DAX - 2 phased weighted average with 2 different weight measures

Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112
  • 2
    I'm not sure I understand what the problem is. If you create a calculated column in the PowerPivot data model, then you can use that column in a pivot table. – Alexis Olson Jan 10 '18 at 16:20
  • 1
    I was looking for calculated fields in Analyze - Calculations - Fields, items & sets, and there it's grayed out. I now understand I need to look for it in Powerpivot - Calculations - Calculated fields. A very banal point, which however I had not found explained clearly until now. Thanks. – Pythonista anonymous Jan 10 '18 at 17:13
  • You can add a column in Power Pivot in "manage" data model and it will be in your pivot table. Another option is go to "Data" tab there you can attach directly the SQL query into a pivot table avoiding Power Pivot and use the calculated field that you know, or use Power Query. – virtualdvid Jan 10 '18 at 20:00

0 Answers0