All geek questions in one place

SSAS: percent of total does not work in hierarchy

I have a simple olap cube - one set of measures and some immeasurable sizes.

I will add one calculation to get a “percentage of total” sales versus gross sales. Code for this calculation:

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

It works.

Within the size of the vault, there is a hierarchy called By State where the vaults are contained internally.

Two questions: 1. Any idea why the calculation will not work when I use the As-As hierarchy, that is, the same calculation, grouped up to the next level?

  • The state problem is aloof, any idea why my total amount shows an error even when I just use the store name?

TIA!

+4
source share
1 answer

In the search process, I found a template in the "calculation tools" called "Percentage of the total." Using this, I translated my calculation into 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!

+4
source

Source: https://habr.com/ru/post/1481686/

More articles:


All Articles