SSAS/MDX: DIVIDE with COUNT overriding FORMAT_STRING?

Question

I have a strange issue with formatting a calculated member in SSAS (multidimensional).

I have a budget amount that's defined on a yearly level and then I'm allocating it to the quarter, month or week level by dividing the yearly amount with the number of members.

When using the SCOPE with the DIVIDE and COUNT MDX function, the defined formatting of the measure (FORMAT_STRING) is no longer applied.

Here is the code and the example:

CREATE MEMBER CURRENTCUBE.[Measures].[VA Budget]
AS 
    NULL,
FORMAT_STRING = "#,#.00",
VISIBLE = 1; 

([Measures].[VA Budget], [Datum].[Datum JQMT].[Jahr].Members) = [VA Jahresbudget];
([Measures].[VA Budget], [Datum].[Datum JQMT].[Quartal].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Quartal]) ));
([Measures].[VA Budget], [Datum].[Datum JQMT].[Monat].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Monat]) ));
([Measures].[VA Budget], [Datum].[Datum JQMT].[Tag].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Tag]) ));
([Measures].[VA Budget], [Datum].[Datum JWT].[Woche].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JWT].CurrentMember, [Datum].[Datum JWT].[Jahr]), [Datum].[Datum JWT].[Woche]) ));

[]

However, it is correctly applied when I'm NOT using the COUNT in the DIVIDE function:

([Measures].[VA Budget], [Datum].[Datum JQMT].[Monat].Members) = DIVIDE([VA Jahresbudget], 12 ));

[]

Also, if I define another calculated member and I refer to the previous one, the formatting is again correctly applied:

CREATE MEMBER CURRENTCUBE.[Measures].[VA Budget2]
AS 
    [Measures].[VA Budget],
FORMAT_STRING = "#,#.00",
VISIBLE = 1;

[]

What's happening here? Is this a bug? How can I make the formatting to be correct without defining an additional calculated member?

(SSAS version is 2016 - 13.0.5206)

lucazav lucazav · Accepted Answer · 2019-07-26T07:43:38

It seems the MDX function Divide() will cause the result to lose the format.

A workaround is to create a new (not visible) calculated member containing only the DIVIDE definition; then use it in your calculated member. Here an example:

CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD]
 AS NULL, 
FORMAT_STRING = "0.0000",
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';


CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD DIVIDE YMD]
 AS DIVIDE (
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Month-Day].[Year YMD],
                                        [Date].[Year-Month-Day].CurrentMember),
                         [Measures].[Dividend]
                    ),
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Month-Day].[Year YMD],
                                        [Date].[Year-Month-Day].CurrentMember),
                         [Measures].[Divisor]
                    )
 ),
FORMAT_STRING = "0.0000",
VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';

CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD DIVIDE YQMD]
 AS DIVIDE (
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Quarter-Month-Day].[Year YQMD],
                                        [Date].[Year-Quarter-Month-Day].CurrentMember),
                         [Measures].[Dividend]
                    ),
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Quarter-Month-Day].[Year YQMD],
                                        [Date].[Year-Quarter-Month-Day].CurrentMember),
                         [Measures].[Divisor]
                    )
 ),
FORMAT_STRING = "0.0000",
VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';

SCOPE ( [Measures].[My measure YTD] );

    SCOPE ( [Date].[Data YMD].MEMBERS, [Date].[Year YMD].[Year YMD].MEMBERS );
        THIS = [My measure YTD DIVIDE YMD];
    END SCOPE;

    SCOPE ( [Date].[Data YQMD].MEMBERS, [Date].[Year YQMD].[Year YQMD].MEMBERS );
        THIS = [Measures].[My measure YTD DIVIDE YQMD];
    END SCOPE;

END SCOPE;

SSAS/MDX: DIVIDE with COUNT overriding FORMAT_STRING?

1 Answers