Post reply

Display measure value based on max value of another measure in SSAS cube

  • September 19, 2014 at 10:31 am

    #148503

    Hi,

    I've one requirement in SSAS cube, to get measure value based on max value of another measure.

    Example :

    Time Measure A Measure B

    1 PM 2944 190.56

    01:00 PM 2,893 174

    01:05 PM 2884 181

    01:10 PM 2920 175

    01:15 PM 2947 175

    01:20 PM 2944 175

    First find the max value of Measure B out of given data set, which is 181(underlined), then take the corresponding value of Measure B which is 2944 and show it on total for that measure(bold out).

    Here Time is a dimension having 1 PM as one of the members.

  • September 22, 2014 at 4:04 am

    #1746976

    From what I can tell, you would just take the max of your first measure over your dimension members and the max of your 2nd member over your dimension members and then do some sort of calculation on it?

    The following example takes the maximum reseller sales year total and the maximum internet orders year total and then multiplies them (in adventure works):

    WITH MEMBER [MaxReseller]

    AS

    MAX

    ([Date].[Fiscal].[Fiscal Year], [Measures].[Reseller Sales Amount])

    MEMBER [MaxInternet]

    AS

    MAX

    ([Date].[Fiscal].[Fiscal Year], [Measures].[Internet Order Count])

    MEMBER MaxCalc

    AS

    MaxInternet*MaxReseller

    SELECT

    {[Measures].[Reseller Sales Amount], [Measures].[Internet Order Count],

    MaxReseller, MaxInternet, MaxCalc } ON 0,

    [Date].[Fiscal].[Fiscal Year] ON 1

    FROM

    [Adventure Works]

    I hope that helps 🙂

    I'm on LinkedIn

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply