Calculated Measure - "% of Grand Total"

# Calculated Measure - "% of Grand Total"

• Wednesday, March 20, 2013 2:22 PM

Hello,

I am trying to replicate the way Excel's pivot "% of Grand Total" works but use it in a calculated measure.

For example, I have 2 dimensions (Product, Team) and 1 fact table. Let's say I have a [Product].[Product Name] along the rows and then a hierarchy of Team (sub-team, sub-sub-team) along the columns. There is an amount measure associated to the dimensions. This is all filtered by day. When I put this in excel and then choose "% of Grand Total" it works perfectly and is exactly what I need. The amount is divided by the total amount per day and each drill down amount is also divided by the total amount per day, which is exactly what I need.

Now when I instead use a subset to find the Top 5 products instead of using the entire [Product].[Product Name], "% of Grand Total" no longer works. What I need is to create a calculated measure that works similarly to "% of Grand Total" or find a way to divide the [Measure].[Amount] by the total amount per day.

For example, let's say for 1/02/2013 the total amount was 100. I need to find the amount/100. Numerator will be [measure].[amount] while the denominator will be total amount of that day.

Date: 1/02/2013

Team1          Team2          Team3          Grand Total

Product1      20/100          15/100         5/100           40/100

Product2      5/100            10/100         20/100          35/100

Product3      10/100          15/100          0/100           25/100

GrandTotal   35/100          40/100          25/100         100/100

Then if you drill down, the sub teams of Team1 should add up to 20/100, etc. So if there are two sub teams for Team1, one may have 5/100 and the other 15/100 for Product1.

Thanks for any help.

### All Replies

• Wednesday, March 20, 2013 3:18 PM

Hi ,

What i understands from your question is you want grant total selected items. Here is approach

While using SUBSET function pass second arguments as 0 i.e. subset([Product].[Product Name],0,10)

so you will get grand total of all product.

If you want grand total of only selected product then you can filter data using EXCEPT or FILTER function .

Thanks,

Swapnil

• Wednesday, March 20, 2013 4:52 PM

Thanks for your help. It got me thinking about how the sets work. So I think I am making progress.

I am using:

CREATE MEMBER [Measures].[Amount_percentage] AS

[Measures].[Amount] / SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] )

*Later I would prefer if I could do the .[All] dynamically (using Axis() function)*

and getting the correct values. This is dividing the amount by the total day amount. This is working on standard fields. I am now introducing a Top 5 product named set: CREATE SET [Top 5 Product] AS TOPCOUNT( [Product].[Product Name].CHILDREN,5,[Measures].[Amount].

Using [Team].[Team Hierarchy] ON columns and [Top 5 Product] ON rows and [Measures].[Amount_percentage]  as my values. I need to filter this by certain product classifications. What is the best way to filter [Measure].[Amount] so I do not pick up amounts in certain classifications? Also, it cannot affect my denominator, as I still need to pick up all amounts for the entire day.

EDIT:

I figured out the filter and came up with this named set:

TOPCOUNT( FILTER([Product].[Product Name].CHILDREN,
[Product].[Classification].&[Class1] or [Product].[Classification].&[Class2]
or [Product].[Classification].&[Class3] or [Product].[Classification].&[Class4]),
25,

CASE WHEN
ISEMPTY( [Measures].[Amount] )
THEN
0
ELSE [Measures].[Amount]
END

HOWEVER, it still takes the amount values from the classifications I do not want. How do I filter the [Measure].[Amount] values to only the 4 classes I want?

One last question to ask..for my calculated measure:

CREATE MEMBER [Measures].[Amount_percentage] AS

[Measures].[Amount] / SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] )

How do I use the Axis() function so that I do not have to use specific members of the dimensions? Is there a way to do something like Axis(0).Item(0).Item(0).Dimension.All? Keep in mind, what I am trying to get out of the denominator is the total amount under all dimensions for the day.

• Edited by Wednesday, March 20, 2013 8:15 PM
•
• Friday, March 22, 2013 5:14 PM

Just wanted to reply that I found an answer just in case someone else could use it. I had to create a named calculation that duplicated my Amount measure, Amount_filtered. I then used that with the SCOPE function in order to filter the measure and divided it with the original total amount, SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] ).

But I do have one question, I didn't want to create another thread. How would I re write the sum above, so it takes into consideration any and all dimension possibilities? So I just want the total Amount sum of the day, not broken down into any dimensions.