How to add a metric for top n count in mdx

Question

I want to create a metric which will show only the top 10 result whenever applied. Suppose the data is

item    Price
A 20
B 45
C 50
D 80
E 10
F 90
G 85
H 55
I 40
J 100

I want to show the top 5 result in descending order. So, the expected result is:

j-100
f-90
g-85
d-80
h-55
other-165

I am already getting the result with the following MDX query:

     With
    Set [Top10] AS
    (TOPCOUNT({ORDER( ({[DimProduct].[item].[All].Children})
     ,([Measures].[Price]),BDESC)},10))

MEMBER [DimProduct].[item].[OtherAll] AS 
(avg({EXCEPT([DimProduct].[item].Members, [Top10])})
)
Select 
[Measures].[ Price] on Columns, 
{
[Top10]
,[DimProduct].[item].[OtherAll]
} on Rows
FROM [testcube]

Result:

j-100
f-90
g-85
d-80
h-55
other-165

I basically want to create a metric with the above query and save it to my cube solution.

So, when I drag item and price it will show all the data i.e all 10 rows.

And, when we drag our newly created metric then it will show top 5 result with the other row (other will be sum of rest of the rows)

j-100
f-90
g-85
d-80
h-55
other-165

Is there any way to achieve this functionality?

Edit 1

Created one dynamic set with top 10
Created calculated measure for others
Created another dynamic set to show both the results i.e top 10 and others.
But when we select the dynamic set to show top 10 + others, it is throwing the error:
A set has been encountered that can not contain calculated members

Did you try using your MDX to create a calculated measure in the cube? — Tab Alleman, Apr 22 '16 at 13:35
What you need here is a dynamic set, NOT a measure, because here you want to reorder the output. You are on track with steps 1 and 3. Not sure why you needed to create a calculated measure. — SouravA, Apr 25 '16 at 07:03
calculated measure is for calculating "other" so that we can create another dynamic set in which we need to show both top 10 and others and can you please provide code sample for whatever you suggested? — Tyler Durden, Apr 25 '16 at 09:21
@TylerDurden this was a question Sourav and me played around with a while back - not sure if it helps as it isn't specific to cube-script code, but maybe there are some alternative approaches in it: http://stackoverflow.com/questions/33485982/top-x-of-top-y-with-restof-member-where-x-and-y-are-hierarchies-from-different-d — whytheq, Apr 25 '16 at 16:24

How to add a metric for top n count in mdx

0 Answers0