sql - 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

Answer 1

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 defined with the given formula on Dim Product level and will be summed further on.