Convert to numeric data type (using MDX)

Question

Hi : I'm using the PowerPivot (Excel) to import data from SSAS (multidimensional). When the data is imported some of the measures are showing "Text" data type.

Would it be possible to assist me to convert the data type to numeric using StrToValue function or other alternatives? Some of the measures are showing text data types are [Measures].[Prev Year Actual], [Measures].[Full Year Forecast] etc.

FULL MDX script is below:

SELECT NON EMPTY { [Measures].[Budget]

   , [Measures].[Actual]

   , [Measures].[Prev Year Actual]

   , [Measures].[Full Year Forecast]

   , [Measures].[Full Year Budget]

   , [Measures].[Budget Variance]

   , [Measures].[Full Year Variance]

   , [Measures].[YTD Actual] } ON COLUMNS

   , NON EMPTY { ([Period].[Period Code].[Period Code].ALLMEMBERS

          * [Period].[Period].[Period].ALLMEMBERS

          * [Cost Centre].[Cost Centre Code].[Cost Centre Code].ALLMEMBERS

          * [Account].[Account Code].[Account Code].ALLMEMBERS

          * [Funded Program].[Funded Program Code].[Funded Program Code].ALLMEMBERS

       * [Funded Program].[Funded Program Short].[Funded Program Short].ALLMEMBERS )

     } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( { [Period].[Period Code].&[202209] } ) ON COLUMNS

      FROM ( SELECT ( { [Period].[Fiscal Year].&[2022], [Period].[Fiscal Year].&[2021] } ) ON COLUMNS

                    FROM ( SELECT ( { [Division QH_ALT_7].[Division QH_ALT_7].&[78_SPS_PRO] } ) ON COLUMNS

                                FROM ( SELECT ( { [Account].[Account].&[MA1_OP] } ) ON COLUMNS

                                             FROM [Management Accounting]))))

WHERE ( [Account].[Account].&[MA1_OP]

     , [Division QH_ALT_7].[Division QH_ALT_7].&[78_SPS_PRO]

     , [Period].[Fiscal Year].CurrentMember )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Answer 1

Although you haven't provided the MDX defining the problematic measures (which from their names certainly look calculated), they also look being numeric at the source already. So it would seem that there's some problem with PowerPivot's import of the query's results, and the obvious idea is to use Power Query for the job.