Calculate the AVG forTopPercentager() in the last 12 past months

[Knut Ivar]

Hi,

(sorry but I didn't finde a way to write my MDX-example against the
Adventure Works DW cube.)

I need to made an calculated member in my cube where I could see the top 10
% Feeding Costs (grouped by FeedingID level). So it should be used as an
benchmark.
This 'KrPrFeedingTop10pct_Avg' works fine for the same month. But I also
need this kinde of benchmark value for the last 12 months. If I brows the
value for the 12Month benchmark in May 2005 it should
show the average value for the top 10% Feeding in the last 12 months .
June 2004 - May 2005. But I don't finde a way to made this calculation.
Think I may should use an decendante() command. But I am not sure how to
write it.

this work fine:
CREATE MEMBER CURRENTCUBE.[MEASURES].KrPrFeedingTop10pct_Avg
AS
avg(toppercent([Feedings].[Feeding].[FeedingID].members,10,[Measures].[KrPrFeeding]),[Measures].[KrPrFeeding]),
VISIBLE = 1;

THis work fine to finde the last 12 months amount of feeding in the region.
CREATE MEMBER CURRENTCUBE.[MEASURES].RegionFeedingLast12Months
AS sum(LastPeriods (12,
Ancestor([Dates].[PeriodMonth].currentmember,[Dates].[PeriodMonth].[Period
Month])) ,[Measures].[RegionFeedingPeriod]),
VISIBLE = 1;

I need to made a new calculated member where I could use some kind of
combination if this two principals. To find the last 12 Months top 10% avg
for Feeding TotalBeop But I think I may is a hard one.

Thanks to any suggestions.

Knut Ivar

[Kevin Yu [MSFT]]

Hi Knut,

I'm not quite familiar with the MDX query, but according to the problem
description, I think you can set the RegionFeedingLast12Months as the set
expression of the AVG function to get a combination of these two principles.

By the way, this newsgroup is mainly for problems for consuming OLAP
services using oledb providers. For the MDX query issue, it would be better
posted in microsoft.public.sqlserver.olap newsgroup. There will be more
professionals there who can answer these questions. Thank you for your
understanding!

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Ohjoo Kwon]

If the two calculated members meet your requirements and I understand what
you mean rightly.

CREATE MEMBER CURRENTCUBE.[MEASURES].KrPrFeedingTop10pct_Avg
AS
avg(toppercent([Feedings].[Feeding].[FeedingID].members,10,
RegionFeedingLast12Months), [Measures].[KrPrFeeding]),
VISIBLE = 1;

Doesn't it make sense?

Ohjoo

Post by Knut Ivar
Hi,
(sorry but I didn't finde a way to write my MDX-example against the
Adventure Works DW cube.)
I need to made an calculated member in my cube where I could see the top 10
% Feeding Costs (grouped by FeedingID level). So it should be used as an
benchmark.
This 'KrPrFeedingTop10pct_Avg' works fine for the same month. But I also
need this kinde of benchmark value for the last 12 months. If I brows the
value for the 12Month benchmark in May 2005 it should
show the average value for the top 10% Feeding in the last 12 months .
June 2004 - May 2005. But I don't finde a way to made this calculation.
Think I may should use an decendante() command. But I am not sure how to
write it.
CREATE MEMBER CURRENTCUBE.[MEASURES].KrPrFeedingTop10pct_Avg
AS
avg(toppercent([Feedings].[Feeding].[FeedingID].members,10,[Measures].[KrPrFeeding]),[Measures].[KrPrFeeding]),
VISIBLE = 1;
THis work fine to finde the last 12 months amount of feeding in the region.
CREATE MEMBER CURRENTCUBE.[MEASURES].RegionFeedingLast12Months
AS sum(LastPeriods (12,
Ancestor([Dates].[PeriodMonth].currentmember,[Dates].[PeriodMonth].[Period
Month])) ,[Measures].[RegionFeedingPeriod]),
VISIBLE = 1;
I need to made a new calculated member where I could use some kind of
combination if this two principals. To find the last 12 Months top 10% avg
for Feeding TotalBeop But I think I may is a hard one.
Thanks to any suggestions.
Knut Ivar