none
Percentage in MDX

    Question

  • Hi,

         How can i find percentage of sales happened of each month per year in MDX?

    I have Time dimension(TimDim) contains members

    1)Year

    2)Month

    And measure is SalesAmount in my Database suppose DB.

    I need output like-:

     Year|Month|MonthlySales|YearlySales|Percentage

    2001|January|300|2000|15%

    etc...

    Sunday, September 09, 2012 9:37 AM

Answers

  • Try this (or equivalent for your cube) ...

    with
    member Measures.[Yearly Sales] as
    IIf([Date].[Calendar].CurrentMember Is [Date].[Calendar].[All Periods],
        ([Measures].[Internet Sales Amount], [Date].[Calendar].CurrentMember),
        ([Measures].[Internet Sales Amount], Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Calendar Year]))
    ), format_string="Currency"
    member Measures.[Pct of Year] as
    [Measures].[Internet Sales Amount] / Measures.[Yearly Sales] //don't need to check for zero denominator
    , format_string="Percent"
    select
    { [Measures].[Internet Sales Amount],
      [Measures].[Yearly Sales],
      [Measures].[Pct of Year]
    } on 0,
    non empty
    [Date].[Calendar Year].[Calendar Year].Members *
    [Date].[Calendar].[Month].Members
    on 1
    from [Adventure Works]
    • Edited by Christian Wade Sunday, September 09, 2012 5:17 PM
    • Proposed as answer by Karthik Rangaraj Sunday, September 09, 2012 8:18 PM
    • Marked as answer by NikkRED Monday, September 10, 2012 9:35 AM
    Sunday, September 09, 2012 4:50 PM