Return Numeric Values from an MDX Measure in Excel Power Pivot
Question details
User needs a Power Pivot calculated measure created in the MDX query designer to return numeric values instead of text, with the correct decimal separator and usable as a numeric data model value.

- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user created an MDX calculated measure in the Power Pivot query designer and is working with an accounting cube or tabular model.
- Observed behavior
- The measure is returned as text, uses an unexpected decimal separator, and cannot be converted to a numeric data model value.
Open the Power Pivot window to inspect the MDX expression and note your current Windows decimal separator before changing formatting, regional settings, or posting a support question.
Check the MDX expression and underlying measure data type
Use this when the measure returns text or fails to convert to a numeric value because the expression or source field may be formatted as text.
The issue usually comes from the MDX expression itself. If the result is wrapped in a text-formatting function or the source measure is not numeric, Excel treats the measure as text instead of a number.
In Excel, go to Power Pivot > Manage to open the Power Pivot window, then select the calculated measure in the calculation area.
Look for MDX functions such as FORMAT, CAST, CONVERT, CStr, or string concatenation in the expression and note where the result is forced to text.
In the Power Pivot data model or underlying cube, confirm the accounting measure is numeric, such as decimal, currency, or integer, and not a text field.
Edit the MDX expression to return a numeric value, then click OK and refresh the Power Pivot connection to update the data model.
Adjust Windows regional settings and Excel decimal separator handling
Use this when the value is correct but the decimal separator appears as a comma instead of a period, or vice versa.
Ask the Excel IT Pro community with full MDX details
Use this when the issue persists or requires provider-specific MDX syntax that depends on the cube and data model configuration.
Use WPS Office as a Free Microsoft Office Alternative for Everyday Excel Workbooks
WPS Office is a free, lightweight Microsoft Office alternative with strong Excel format compatibility, a familiar ribbon UI, and seamless migration for standard workbook tasks. It does not include Power Pivot MDX query designer but handles everyday spreadsheets, formulas, pivot tables, and charts without the need for MDX complexity.
- 1. Download WPS Office: Go to the official WPS website and download WPS Office for Windows or Mac, then complete the installation.
- 2. Open your Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets, click Menu > Open, and select the .xlsx or .xls workbook to verify formulas, formatting, and charts.
- 3. Use standard analysis tools: Use Insert > PivotTable or the Data tools in WPS Spreadsheets for common analysis tasks instead of Power Pivot MDX models.

Frequently Asked Questions
Why does my Power Pivot calculated measure return text instead of a number?
Usually the MDX expression is formatting or casting the result as text, or the source measure is a text type. Open Power Pivot > Manage, inspect the expression for FORMAT, CAST, CONVERT, or string concatenation, and verify the underlying measure data type.
How do I change the decimal separator used by Power Pivot in Excel?
The separator comes from Windows regional settings. Open Control Panel > Region > Formats > Additional settings, adjust Decimal symbol, restart Excel, and refresh the Power Pivot connection.
Does WPS Office support Power Pivot data models?
No. WPS Office does not include Power Pivot or MDX query designer. It opens standard Excel workbooks, including .xlsx and .xls files, formulas, pivot tables, and charts, but Power Pivot-specific model connections are not supported.
Where can I get specialist help for MDX query designer issues?
Ask in the Excel IT Pro TechNet community. Include the full MDX expression, provider details, regional settings, and measure data types so specialists can give appropriate guidance.
Do I have to recreate all my Excel files if I switch to WPS Office?
No. WPS Office supports .xlsx and .xls formats and preserves common workbook content, formulas, and formatting. However, Power Pivot/MDX models may not be available in WPS Spreadsheets.




