MS SQL Server OLAP - percentage based on the current filter

Author

percentage based on the current filter

gabriele

2006-01-24, 9:24 am

Hi to all,
I built a calculated member showing the percentage of a certain
measure over more dimensions.
For example,
I have 3 dimensions, d1,d2 and d3 and a measure m1. My calculated
member (called P), is the percentage of m1 on the total.

So, when I don't apply any filter on dimensions d2 and d3 i have the
following result:
-----------------------------------------------------------------------------------------------------------------
All dim2
All dim3
-----------------------------------------------------------------------------------------------------------------
M P (%)
All dim1 20 100%
1st member of d1 2 10%
2nd member of d1 6 30%
3rd member of d1 4 20%
4th member of d1 8 40%
-----------------------------------------------------------------------------------------------------------------
and it works fine.

But, assuming d2 is a time dimension, when I filter d2 = 2005 instead
of "All times", the previous table shows all the values considering
only the year 2005:

-----------------------------------------------------------------------------------------------------------------
dim2 = 2005
All dim3
-----------------------------------------------------------------------------------------------------------------
M P (%)
All dim1 5 25%
1st member of d1 0 0%
2nd member of d1 1 5%
3rd member of d1 1 5%
4th member of d1 3 15%
-----------------------------------------------------------------------------------------------------------------

Now my question is:
how can I have a percentage Q that considers filtered and shown data
always if they were the 100% of the measure? I would like to have, in
the second example, the following result:

-----------------------------------------------------------------------------------------------------------------
dim2 = 2005
All dim3
-----------------------------------------------------------------------------------------------------------------
M P (%)
All dim1 5 100%
1st member of d1 0 0%
2nd member of d1 1 20%
3rd member of d1 1 20%
4th member of d1 3 60%
-----------------------------------------------------------------------------------------------------------------

Any suggestion will be valued!!!!!
Gabriele

Deepak Puri

2006-01-26, 4:58 pm

Hi Gabriele,

If you could give an idea of the MDX you used in the calculated measure,
it may provide some more clues.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

gabriele

2006-01-28, 1:23 pm

I used the following MDX:
"([Measures].[M])/([Measures].[M],[dim1].DefaultMember,[dim2].DefaultMember,[dim3].DefaultMember)"
Thanks you a lot in advance!

Deepak Puri

2006-01-29, 3:23 am

Since you're interested in percentage of total on rows of the query
(regardless of filter conditions), try an approach like this sample from
Tom Chester:

http://www.tomchester.net/articlesd...les
.html[color=darkred]
Flexible Percent of Total

Works with any set and any measure (assumes measure being operated upon
is in first column)

WITH MEMBER Measures.[% Total] AS

' ( StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember, --
cur row mbr

StrToSet("Axis(0)").Item(0).Item(0) ) / --
1st measure on cols

( StrToSet("Axis(1)").Item(0).Item(0), --
assume 1st row=total

StrToSet("Axis(0)").Item(0).Item(0) ) ' , --
1st measure on cols

Format_String = '0%'

SELECT

{ [Store Sales] , [% Total] } ON AXIS(0),

{ Product.DefaultMember, [Product Family].Members} ON AXIS(1)

FROM Sales[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links