SSAS: Percent of Total not working in hierarchy

Question

I have a simple olap cube - one set of measures and some unexciting dimensions.

I've add one calculation to get the "percent of total" sales against the gross sales measure. The code for this calculation is:

 ([Dim Stores].[Store Name].CurrentMember, [Measures].[Gross Sales]) 
 / 
 ([Dim Stores].[Store Name].Parent, [Measures].[Gross Sales])

This works.

Within the store dimension, there is a hierarchy called 'By State' where the stores are contained within.

Two questions please: 1. Any idea why the calculation would not work when I use the the 'By state' hierarchy i.e. the same calculation grouped by the next level up?

The state problem aside, any idea why my grand total shows an error even when I just use the Store Name?

TIA!

Just wondering - are both my problems the same thing - are totals simply not working? — Warren

Warren Warren · Accepted Answer · 2013-05-20T03:22:10

In poking around, I found a template within the "calculation tools" called "Percentage of Total". Using it, I translated my calculation to this:

Case
// Test to avoid division by zero.
When IsEmpty
     ( 
        [Measures].[Gross Sales]
     ) 
Then Null

Else ( [Dim Stores].[By State].CurrentMember, [Measures].[Gross Sales] ) 
     /
     ( 
       // The Root function returns the (All) value for the target dimension.
       Root     
       ( 
          [Dim Stores]
        ), 
        [Measures].[Gross Sales] 
     )

End

It worked!

SSAS: Percent of Total not working in hierarchy

1 Answers