Search code examples
ssasmdx

MDX - How skip 0 values in MIN agregation and how exclude some percent of results?

In my cube I have

  • Earnings as measure with MIN aggregation
  • Dimension: [Localization].[Type].&[center]
  • Dimension: {[Date].[Year].&[2017], [Date].[Year].&[2018]}

My query is: 

What are the minimum earnings of the person who decides to buy
apartments in the city center, excluding 5% of the lowest, within
last 2 years?

Now my mdx query looks like that:

SELECT
[Measures].[MinEarnings] ON COLUMNS
FROM [cube]
WHERE
(
    BottomCount ([Localization].[Type].&[center], 95, [Measures].[MinEarnings]),
    {[Date].[Year].&[2017], [Date].[Year].&[2018]}
)

I have two problems:

  1. Some earnings are 0 - how can I skip them in calculations?
  2. If my query correctly excludes 5% of the lowest earnings?
Solution

  • First of all you should use toppercent not bottomcount. you want the min salary of a person who is not in last 5% not last 5. Toppercent will give you the top 95%.

    Secondly to filter 0 you can use the following syntax

    toppercent (
filter([Localization].[Type].&[center], [Measures].[MinEarnings]>0)
, 95, [Measures].[MinEarnings])

    Even now placing the code in the where clause might not work, however try it. I would suggest that you move the toppercent to rows , then order it, then take the top1 

    topcount(
order(
toppercent (
filter([Localization].[Type].&[center], [Measures].[MinEarnings]>0)
,95, [Measures].[MinEarnings])
,[Measures].[MinEarnings],asc)
,1)

    I have an example which gives the minum sales amount of cities, notice i have replaced nulls with 0 to make it as close as possible to your case

    with member [Measures].[Internet Sales Amount2] as case when ([Measures].[Internet Sales Amount])=null then 0 else [Measures].[Internet Sales Amount] end 

    select [Measures].[Internet Sales Amount2]
on columns ,
topcount(order(toppercent(filter([Customer].[City].[City],[Measures].[Internet Sales Amount2]>0),95,[Measures].[Internet Sales Amount2]),[Measures].[Internet Sales Amount2],asc),1)
 on rows 
from [Adventure Works]
where [Customer].[Country].&[Canada]

    in the picture below is the result before topcount 1 enter image description here