I have an MDX query that I've pasted into Powerpivot. When I turn this data into a pivot table in excel (by using Powerpivot) some fields that are numbers are instead showing as text. When I drag them to the 'values' section of the pivot table the aggregation is by 'Count' and when I try to change the aggregation to 'Sum' I get the error:
We can't summarize these field by Sum because it's not a supported calculation for text data types.
This doesn't happen when I take an alternative route (not involving Powerpivot) and connect directly from Excel to the A/S database and create these same calculations using MDX. The fields in question have either a number or null in them. Is it these nulls that are causing Powerpivot to interpret these as text fields?
How do I get the pivot table created by Powerpivot to recognize my numeric fields as numeric and not treat them like text? Do I really need to turn all nulls to zeros?