Hi,
I am displaying the following dimension in a PivotTable: Page
Filter(Office,Period,Charge Type), Row(Country), and Column(Charge Amt, % of
Total).

The calculate measure for "% of Total" does not work

% of Total = [Measures].[Charge Amt]/([Measures].[Charge Amt],
Ancestor([COUNTRY].CurrentMember,[COUNTRY].[(All)])

Not sure how to correct this problem or what is the correct syntax. Any
insight or guidance is greatly appreciate it.

What errors are you experiencing - can you give an example?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Thanks for taking the time to respond to my post/question. Here is a little
info on my OLAP cube.

DIMENSIONS
---------------
Period
Customer
Charge Type
Region
Country
Sector
Sales Person
Sales Trader
Customer Type
Tier
Office
Desk

MEASURES
---------------
Charge Amt
Volume Amt

CALCULATED MEMBERS
---------------------------
Prev Charge Amt
Prior Year Charge Amt
YoY Change
Ytd Charge Amt
Prior Ytd Charge Amt
% of Total Charge Amt
Variance

The problem that I am having is that when I select another dimension, for
example Customer, the "% of Total..." displays a 1 or #NUM. However, if I use
Country for the Row, the numbers are calculated correctly.

Here is the formula for

% of Total Charge Amt = ([CHARGE TYPE].CurrentMember,[Measures].[Charge
Amt])/([Measures].[Charge Amt],
Ancestor([COUNTRY].CurrentMember,[COUNTRY].[(All)]))

I want to be able to switch dimension for the row...

Regards,
Fernando Sanchez
"Deepak Puri" wrote:

Hi Feranando,

You can try something like:

[Measures].[Charge Amt])/
([Measures].[Charge Amt],
StrToSet("Axis(1)").Item(0).Item(0).Dimension.Defa ultMember)

There are earlier discussion threads in this newsgroup discussing
similar requirements. The section on "Flexible Percent" below may also
give you some ideas:

http://www.tomchester.net/articlesdo...desamples.html

Pushing the MDX Envelope

Code Samples
...
Flexible Percent of Total

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

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,
This formula works great with two dimensions on the Row of the PivotTable.
What change do I make for another dimension.

Regards,
Fernando Sanchez
"Deepak Puri" wrote:

Hi Fernando,


Can you give an example of how you want the percent computed with
multiple dimensions on rows - should it be based on the grand total for
all row dimensions in denominator, or just based on 1 row dimension?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Here is how the PivotTable looks like:
PageFilter
-----------
PERIOD = 2005
OFFICE = ALL OFFICE
CHARGE TYPE = GROSS COMMISSION

ROW
-----
DESK
COUNTRY

COLUMNS
------------
CHARGE AMT
% OF TOTAL

HERE IS THE RESULTS I WOULD LIKE TO SEE
-----------------------------------------------------
CHARGE AMT % OF TOTAL
---------- ----------
LDN
CHILE 200 0.264900662
MEXICO 50 0.066225166
PERU 125 0.165562914
USA 375 0.496688742
OTHER 5 0.006622517
LDN TOTAL 755 0.668141593
NY
CHILE 175 0.466666667
MEXICO 25 0.066666667
PERU 100 0.266666667
USA 50 0.133333333
OTHER 25 0.066666667
NY TOTAL 375 0.331858407
GRAND TOTAL 1130 1.0

I am interested in seeing the % of total for each country of the sub totals
and then the sub total of the grand total that if it is possible. Also what
if I add the DESK to the column and leave the country on the row. What will
happen?

If I am not mistaken, the currently % of total for country is calculated on
the sub total.

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Deepak,
In my last respond, I forgot to show the third dimension, which is the
country group.

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Fernando,


Starting from your example, I created a calculated measure and cell for
Foodmart Sales, which seem to work for 1 level of drill-down. I based
the calculated cell on this post in Chris Webb's blog:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
130.entry


The Foodmart query below defines a calculated measure: [RollupFraction]
and cell: [LowestDim]. But I was also able to add these to the Sales
cube and create an equivalent Excel pivot table:

MEMBER [MEASURES].[RollupFraction] AS '2'
CELL CALCULATION [LowestDim] FOR '({[MEASURES].[RollupFraction]})' AS
'IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrT oSet("Axis(0)").Item(0
).Count-1)), NULL,
iif(CALCULATIONPASSVALUE([MEASURES].[RollupFraction], -1, RELATIVE) < 2,
CALCULATIONPASSVALUE([MEASURES].[RollupFraction], -1, RELATIVE),
iif(CALCULATIONCURRENTPASS() = 129, 1,
iif(Intersect(Extract(StrToSet("Axis(1)"),
StrToSet("Axis(1)").Item(0).Item(128-CALCULATIONCURRENTPASS()).Dimension
),
{StrToSet("Axis(1)").Item(0).Item(128-CALCULATIONCURRENTPASS()).Dimensio
n.Parent}).Count= 0, 2,
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count-1)/
(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0 )").Item(0).Count-1),
StrToSet("Axis(1)").Item(0).Item(128-CALCULATIONCURRENTPASS()).Dimension
..Parent)))))',
CALCULATION_PASS_NUMBER=129, CALCULATION_PASS_DEPTH=128,
SOLVE_ORDER=2, FORMAT_STRING = 'Percent',
CONDITION='CALCULATIONCURRENTPASS()>(128-StrToSet("Axis(1)").Item(0).COU
NT)'

SELECT {[Measures].[Unit Sales], [MEASURES].[RollupFraction]} ON
columns,
Non Empty CrossJoin([Store Type].Members,
CrossJoin([Education Level].Members,
[Yearly Income].Members)) on rows
FROM SALES

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Thanks for getting back to me...
I don't have any experience with MDX. I am guessing that formual below is
MDX. I cut and pasted the it in the MDX Sample Application but I received an
error. How do I go about creating or successfully executing your example to
see the results. I only have experience with creating calculated measures
in the cube.

Regards,
Fernando Sanchez

"Deepak Puri" wrote: