CODEWITHSUNDEEP
sqlpivotssasmdx
aruydzi
aruydzi

Reputation: 97

SSAS force aggregation of the results of calculated measure

I have a calculated measure in SSAS cube used in excel pivot table. My issue is that the calculated measure treats totals as lines and applies the formula to the aggregated totals of the fields. An example using order lines:

Order1 A B C D
product1 1 40 0 0
product2 40 1920 20 960
product3 1 24 0 0
product4 2 32 0 0
product5 2 27 0 0
product6 1 760 0 0
product7 1 2880 1 2880
product8 1 872 0 0
product9 2 1680 0 0

The calculated measure is Column D with the following formula: B / A * C

At the products level, column D shows the desired values. At the Order1 level, column D aggregates to 3390.96 while the actual total of column D is 3840.

SSAS first aggregated the columns A,B,C and then applies the formula on the aggregated values to calculate the total of column D.

Is it possible to calculate the totals of the calculated measure using the sum of lines?

Thank you

Upvotes: 0

Views: 530

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

There is a trick to do this in SSAS MD - I call it "fictitious measure", please find explanation below.

  • Create a dumb measure in the measure group with A, B, C of your example always returning NULL. Aggregation function - sum, because you requested it to be sum in your question.

  • Define a SCOPE on this measure like

    SCOPE ([Dim Product].[Product].Members, [Measures].[Dumb Measure]); 
    This = [Measure].[B] / [Measure].[A] * [Measure].[C];
END SCOPE

This measure will be calculated with the SCOPE defined formula on Dim Product level and will be summed further on.

Upvotes: 0

Related Questions