SSRS MDX Calculated Member Ignoring Filters

0
votes

Using the Query Designer in VS2012 I'm creating a report about chickens.

I select my measure (Count of Chickens) and the Dimension (Farm).

I use the filter expression option to create a parameter for chicken colour, and only include chickens that are red, blue or green. This gives me something that looks a little like this:

Farm    |Chicken Count  
A       |3
B       |4
C       |19

Now I want to show this count of chickens as a percentage of the total number of chickens that farm has. Something like this:

Farm    |Chicken Count  |Percentage of Total Chickens at Farm
A       |3              |0.13
B       |4              |1
C       |19             |0.45

How can I create a calculated Member to show this last field?

I've tried

[Measures].[Chicken Count] / SUM(Axis(1), [Measures].[Chicken Count])

but that gives me the farms chicken count as a percentage of all the chickens at all the farms meeting the parameter.

The problem I'm facing is that I can't find a way to get all the chickens for the farm - ignoring the filter.

How can I do something like

[Measures].[Chicken Count] / SUM(non-filtered Chickens)?

Please let me know in comments if there is anything I can do to help explain this further. Please also assume (if it isn't already clear) that I have almost no knowledge of MDX.

2
reporting-servicesmdx

2 Answers

1
votes

Add a calculated measure to your query, using the following expression:

[Measures].[Chicken Count] / ([Measures].[Chicken Count], [Chicken].[Colour].[All])

The tuple in the denominator removes the local filtering of chickens by colour (assuming the name of your dimension is called 'Chicken' and the attribute is called 'Colour'), returning the total count of chicken in the given context.

Similarly, if you wanted a farm percentage of total number of chickens, you would do this:

[Measures].[Chicken Count] / ([Measures].[Chicken Count], [Chicken].[Colour].[All], [Farm].[Farm].[All])
0
votes

Assuming your filter is based on a [Color] Dimension and you're filtering for Brown chickens only, you can define it something like this:

With 
  Member [Measures].[Brown Chickens] as ( [Measures].[Chicken Count], [Color].[Brown])
  Member [Measures].[All Chickens] as ( [Measures].[Chicken Count], [Color].[All])
  Member [Measures].[Percentage of Count] as [Measures].[Brown Chickens]]/[Measures].[All Chickens]
Select 
  [Farm].Children on Rows, 
  { [Measures].[Brown Chickens], [Measures].[Percentage of Count] } on Columns
From [myCube]