CODEWITHSUNDEEP
ssasolapcube
Warren
Warren

Reputation: 2014

SSAS: Percent of Total not working in hierarchy

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?

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

TIA!

Upvotes: 5

Views: 2394

Answers (1)

Warren
Warren

Reputation: 2014

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!

Upvotes: 5

Related Questions