MDX Calculated Measures not affected by slicers

Question

I'm new with this howl MDX thing and cant seem to figure out how to make the following work.

I have a few Calculated measure that calculates a value based on the line related to the rest of the data. eg. The total contribution of sales a single store has to the total of all stores.

Here are 2 Expressions that makes this possible:

([Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
([Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])

The problem I'm having is that my slicers do not affect the results. In other words: Lets say a store has a 10% contribution with regards to the total of 100 store (all stores). When filtered by region (for example) it lowers the total number of store to 50, but the contribution still shows 10%. I need the contribution to be relative to the 50 store not the 100.

For some reason the date seems to work, but this is probably because it does not affect the axis.

I'm not sure how the cube (built in SSAS) construct the query behind the scenes but I would imagine it looking something like this:

WITH
MEMBER
[Measures].[%GT Total]
AS
(
[Measures].[Total] / MAX([Store].[Store ID].ALLMEMBERS,[Measures].[Total])
--[Measures].[Total] / SUM([Store].[Store ID].[All],[Measures].[Total])
),
FORMAT="Percent"

SELECT
{[Measures].[Total],[Measures].[%GT Total]} ON AXIS(0)
,NON EMPTY{[Store].[Store ID].CHILDREN} ON AXIS(1)
FROM
[RMS_BISale]
where 
[Store].[Division].[Division ID].[Wholesale]

another example will be the Store Average which is also not affected by slicers

(AVG([Store].[Store ID].[Store ID].members, [Measures].[Total]))

or the region division total of a store... they all seem to have the same problem.

MyCube structure

Any help\guidance will be very much appreciated.

its the same\similar issue as in the below link, but the answer there gives me the same result as what I get now: MDX ignoring Excel filter

whytheq whytheq · Accepted Answer · 2016-10-08T16:09:51

In terms of the straight mdx does adding EXISTING help?

WITH
MEMBER
[Measures].[%GT Total]
AS
(
[Measures].[Total] 
/ MAX(EXISTING [Store].[Store ID].ALLMEMBERS,[Measures].[Total])

),
FORMAT="Percent"

SELECT
{[Measures].[Total],[Measures].[%GT Total]} ON AXIS(0)
,NON EMPTY{[Store].[Store ID].CHILDREN} ON AXIS(1)
FROM
[RMS_BISale]
where 
[Store].[Division].[Division ID].[Wholesale];

MDX Calculated Measures not affected by slicers

1 Answers