locked
Percentage KPIs in SSAS 2005 RRS feed

  • Question

  • Hi,

    Is it possible to create a KPI which shows how much percent is the value of the current cell with respect to its immediate parent.
    eg.

    Dimensions              Values       Percent(KPI)
    Parent1                         60                   60%
       Child1                        30                   50%
          Child11                   10                   33.3%
          Child12                   10                   33.3%
          Child13                   10                   33.3%
       Child2                        30                   50%

    Parent2                         40                   40%
       Child1                        10                   25%
          Child11                     5                   50% 
          Child12                     5                   50%
        Child2                       30                   75%
          Child21                   15                   50%
          Child22                   15                   50%
          
    Total                           100                  100%

    Thanks for your help.

    Max.
    Tuesday, December 15, 2009 8:59 AM

Answers

All replies

  • If you define a measure which calculates the %-Use it should be possible or?
    Maybe with something like this http://sqlblog.com/blogs/mosha/archive/2008/09/11/ratio-to-parent-on-rows-in-mdx.aspx

    Let me know if it helped you.

    Sven
    • Proposed as answer by Sven Bossenmaier Tuesday, December 15, 2009 11:55 AM
    • Marked as answer by Max Parker Tuesday, December 15, 2009 12:01 PM
    Tuesday, December 15, 2009 10:19 AM
  • Hi Sven,

    Thanks for the response, will try it out. I think that approach should work fine for me.

    Max
    Tuesday, December 15, 2009 11:47 AM
  • I created a sample KPI as shown below in Adventure Works 2008 and got it working.

    Step #1:Created a KPI called "Ashok KPI" with MDX for Value Expression

     ([Account].[Accounts].

    Currentmember,[Measures].[Amount])/([Account].[Accounts].Currentmember.Parent,[Measures].[Amount])

    Step #2:  executed the MDX in SSMS

    Select

     

    [Account].[Accounts].Members On Rows,

    {Measures.Amount,

    KpiValue( "AshokKPI" )} On columns

    From

     

    [Adventure Works]

    Since this is a test KPI, I did not handle for Balance sheet level. Other than this, it worked perfectly.


    Thanks, Ashok -http://dugaputiashok.blogspot.com/
    Tuesday, December 15, 2009 12:12 PM