SSAS force aggregation of the results of calculated measure

Question

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

Ferdipux Ferdipux · Accepted Answer · 2021-01-11T08:06:02

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.

SSAS force aggregation of the results of calculated measure

1 Answers