SSAS How to Multiply two calculated measures without multiply the grand total (Excel)

0
votes

I have two measures, "Budget Qty" and its aggregation is SUM, and "_NSPPrice" and it is a calculated measure (Multiply another 3 measures). and I have created a new calculated meausre "Budget Amount (USD)" by multiply "Budget Qty" and "_NSPPrice" and the result is correct except the grand total, it multiplies the total of "Budget Qty" and total of "_NSPPrice" but I want the total to be the sum of "Budget Amount(USD)".

enter image description here

1
excelpivot-tablessasmdxolap-cube
can you show the calculate measure mdx code ? – ali azizan
if you mean Budget Amount(USD) it is as following Budget Qty * _NSPPrice – emad ahmed

1 Answers

0
votes

For this, you could use the scope function to change the sum function of the measure. Try something like

SCOPE([Product].[Product].[All],[Measures].[Budget Amount(USD)]);
    THIS = SUM([Product].[Product].[Product],[Measures].[Budget Amount(USD)]);
End SCOPE;

See Scope reference: Scope function