I am looking to write a query that returns a result set that in effect is the Top 5 Stores based on sales of the Top 10 Selling Products. I am struggling with the MDX. I was trying different combinations of TopCount and Filter functions but with not much success. Any samples are appreciated. thanks.
select top 5 s.store_id, sales = sum(sps.sales)
from stores s, storeproductsales sps
where sps.product_id in
(select top 10 product_id from productsale order by sales desc)
and s.store_id = sps.store_id
group by s.store_id
order by sum(sps.sales) desc
If you don't have the totals in tables already then create derived tables to do it.