September 21, 2015 at 4:51 am
Hi,
I am trying to get the maximum sales for stores where there exists more than one store in the same region (e.g. top sales) using MDX queries.
I want to leave the ones where there is only one per region out of calculation.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
Any help would be appreciated. Must be done using MDX.
September 22, 2015 at 7:11 am
I think that this technique (using AdventureWorks as an example) does what you are looking for. You should be able to adapt it to your needs (providing you have a level above store in your hierarchy):
WITH MEMBER SPCount AS
[Geography].[Geography].CURRENTMEMBER.PARENT.CHILDREN.COUNT
SELECT
{[Measures].[Reseller Sales Amount]} ON 0,
NON EMPTY
GENERATE (
[Sales Territory].[Sales Territory].[Country].MEMBERS,
[Sales Territory].[Sales Territory].CURRENTMEMBER
*
TOPCOUNT(
FILTER([Geography].[State-Province].[State-Province].MEMBERS, [SPCount] > 1),
1,
[Measures].[Reseller Sales Amount]
)
)
ON 1
FROM [Adventure Works]
So in the example above, it excludes a sales territory (United Kingdom) as it only has 1 "State-Province" (England).
It's a bit of a fudge but frankly, I don't care 😛
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply