Numbers showing as text from MDX query


  • 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?


    Friday, September 20, 2013 5:00 PM


  • Hi JohnZofo,

    In Powerpivot window, select menu home and change data type to "Decimal Number".


    • Marked as answer by JohnZofo Saturday, September 21, 2013 1:26 PM
    Saturday, September 21, 2013 4:42 AM

All replies