locked
How to create a Fixed Target Value comes from SSAS Datasource in PPS 2010 RRS feed

  • Question

  • Hi All,

    I have a KPI which contains of one actual data comes from SSAS datasource, and the target value also comes from SSAS datasource. The actual is the Amount, and the target is the average of these amounts. The problem is that, when I attach this KPI to a scorecard and adding a dimension to showing some of members. It filters both the actual and target values (to be average of the selected members only) and I just need to filter the actual only and the target to be fixed. As, I need to show the variance between the member amount against the whole members average. Is there anyway to can accomplish this ?

    Thanks,
    Fouad

    Thursday, February 23, 2012 8:48 AM

Answers

  • Multiple ways of doing it:

    1. Create a Calculated measure in your cube as:

    WITH MEMBER Measures.[AvgAmount] as
    AVG ( [Measures].[Amount], [dimTypes].[All] )

    2. When defining Target value in KPI, check MDX Tuple formula and enter:

    AVG ( [Measures].[Amount], [dimTypes].[All] )

    3. When defining Target value in KPI, select Amount measure and in dimension select Types and include either all or required members.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by FMahmoud Tuesday, February 28, 2012 9:57 AM
    Monday, February 27, 2012 6:34 PM

All replies

  • Not sure if I am completely following this.  Can you please provide an example of what you are currently seeing and what the expected results are?

    Maybe you just need to use an MDX expression for the Target value to get the parent of the current dimension you are evaluating.  Not completely sure I am following what you are trying to accomplish though or how you have the actual and targets setup.  Please provide some additional details of how you have this configured and what you would like the results to be. Thanks.


    Dan English's BI Blog

    Thursday, February 23, 2012 11:55 AM
  • I addition to what Dan said, I would say you need to check how your target measure is setup.

    You can either change the cube and modify the calculation to always include all dimension members used in average

    OR

    In Dashboard Designer create a calculation under Target to slice on all members like ( [Measures].[Target] , [Dimension].[All] ). This should override any filtering being done on scorecard.


    http://dailyitsolutions.blogspot.com/

    Thursday, February 23, 2012 4:45 PM
  • Thanks Dan and Umair for your support, and sorry for my bad explanation as I'm a SSAS and PPS newbie, please find my sample below.

    Transactions (Fact):
    ID | Amount | Type
    1  |    20   |   1
    2  |    20   |   1
    3  |    20   |   1
    4  |    10   |   2
    5  |    10   |   2
    6  |    10   |   2

    Types (Dimension)
    1  |   On-Us
    2  |   Off-Us

    I made a kpi that the actual is the amount and the target is the average of all the transactions amounts, then I made a scorecard and attach this kpi to it. When I put the dimension to be the last child of the scorecard and select only type 1 in the members list the target is calculated to be only average of type1 transactions. What I need is to make the target is a fixed calculated value regardless the selected dimension members. In my case the target should be 15 = 90 / 6 regardless any amended filtration. When I choose only type 1 the target is changed to be 20 = 60 / 3.
    I have monitored the queries sent to SSAS in sql profiler I found that all the queries are containing the selected members :(

    Thanks,
    Fouad


    • Edited by FMahmoud Monday, February 27, 2012 1:19 PM
    Monday, February 27, 2012 1:18 PM
  • Multiple ways of doing it:

    1. Create a Calculated measure in your cube as:

    WITH MEMBER Measures.[AvgAmount] as
    AVG ( [Measures].[Amount], [dimTypes].[All] )

    2. When defining Target value in KPI, check MDX Tuple formula and enter:

    AVG ( [Measures].[Amount], [dimTypes].[All] )

    3. When defining Target value in KPI, select Amount measure and in dimension select Types and include either all or required members.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by FMahmoud Tuesday, February 28, 2012 9:57 AM
    Monday, February 27, 2012 6:34 PM
  • Thank you a lot Umair, did it finally :) by step 3 only, the other 2 ways aren't working right as they gave me the Total Amount instead of average and below is the sent query from PPS to SSAS via the SQL server profiler. I don't know why, if you can tell me it will be so appreciated.

    WITH
     MEMBER [Measures].[CUSTOM_c469341f-c623-fd8f-e803-ca589cc9c60e_d6bfc488-d649-4f19-8aee-ec5f0cdcd9d3] as '(AVG ( [Measures].[Amount], [Dim Types].[All] ))'
     MEMBER [Measures].[c469341f-c623-fd8f-e803-ca589cc9c60e_d6bfc488-d649-4f19-8aee-ec5f0cdcd9d3] as '([Measures].[CUSTOM_c469341f-c623-fd8f-e803-ca589cc9c60e_d6bfc488-d649-4f19-8aee-ec5f0cdcd9d3])'
     MEMBER [Measures].[c469341f-c623-fd8f-e803-ca589cc9c60e_f9ec096b-e9a1-4c05-a642-d9843094cd83] as '([Measures].[Amount])'
    SELECT { [Measures].[c469341f-c623-fd8f-e803-ca589cc9c60e_d6bfc488-d649-4f19-8aee-ec5f0cdcd9d3],
    [Measures].[c469341f-c623-fd8f-e803-ca589cc9c60e_f9ec096b-e9a1-4c05-a642-d9843094cd83]} ON AXIS(0),
    {([Dim Types].[Type].[All])} ON AXIS(1)
    FROM [Cube Test]

    Thanks,
    Fouad

    Tuesday, February 28, 2012 10:02 AM
  • You're welcome.

    I was wrong with the [All] member. The reason you are getting sum of total amount as [All] is considered to be a member of hierarchy. Meaning AVG function is only being calculated across one member therefore total amount.

    However if you give specific members like [Dim Types].[Type].&[On-Us] then Average would be calculated across the specific members. So if you need to create a calculated measure you could use:

    WITH MEMBER Measures.[AvgAmount] as
    AVG ( [Measures].[Amount], { [dimTypes].[Type].&[On-Us], [dimTypes].[Type].&[Off-Us] } )



    http://dailyitsolutions.blogspot.com/

    Tuesday, February 28, 2012 3:43 PM