Grand total on a SSAS driven pivot table

0
votes

We are using a SASS SSAS driven pilot table where we would like to add a calculated measure using the sum of a column. We don't know in advance which filters the users will be using, the measure is valid for any of them, so we just tried adding a calc measure on the excel file such as

[Measures].[Sales]/sum(axis(1),[Measures].[Sales])

But it doesn't seem to work. So just to test we started with a plain

sum(axis(1),[Measures].[Sales])

which gives some astonishing result. We cannot figure out what the result is adding up! Applying some filters and a detailed row, we get exactly twice the expected value (!!??). Applying exactly the same filters and filtering also the rows, we get a value that we are not able to guess where it comes from.

Any idea about what MDX should we use?

Edited to clarify: We want to add the calculated measure on an Excel pivot table, not on the SSAS olap cube definition.

1
excelssas
Please clarify what technologies you're actually using. Sass is a CSS preprocessor. – cimmanon
oooops! Sorry, my fault. We are using Microsoft SSAS (Sql Server Analysis Services) and not SASS, – DeepButi

1 Answers

0
votes

I obviously was having a bad day. One cannot think excel-like to solve an mdx issue.

A simple

([MyDim].[MyHierarchy].currentmember.parent,[Measures].[Sales])

is the right syntax to use in our calculus as the "parent total".

Thks