Trouble with calculated member always equals to zero

Question

I have a simple fact table with AverageBalance column, TimeKey (it is always end of month date), AccountKey and AccountClassKey.

And I have added calculated member that will calculate average balance on different levels of time hierarchy.

CREATE MEMBER CURRENTCUBE.[Measures].[Average Calc] AS 
    Avg(
      EXISTING([Time].[Processing Date].[Processing Date].MEMBERS) ,
      [Measures].[Avg Bal]
    ) ;

And it works fine.

But I have a problem of adding following logic to the calculation: Based on AccountClassKey Average Balance should be either added or subtracted from the rest of the Average Balance amount.

So I tried something like this (first I have added Account Class Key as a measure):

CREATE MEMBER CURRENTCUBE.[Measures].[AvgBalMult] AS
[Measures].[Avg Bal] * (
       CASE 
       WHEN [Measures].[Account Class Key] = 1 THEN 1  
       WHEN [Measures].[Account Class Key] = 2 THEN -1
       else 0
       END);

But I am always getting zeros in the report when I aggregate over Time and I realized that it is because Account Class measure is getting aggregated first and therefore it will never have the value of 1 or 2, unless I am looking at account level only.

At the end I would like to get something like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Calc] AS 
    Avg(
      EXISTING([Time].[Processing Date].[Processing Date].MEMBERS) ,
      [Measures].[Avg Bal] * (
       CASE 
       WHEN [Measures].[Account Class Key] = 1 THEN 1  
       WHEN [Measures].[Account Class Key] = 2 THEN -1
       else 0
       END)
    ) ;

On the account level it will give me Average Balance without sign, but when aggregating it should aggregate with the logic above.

Answer 1

You can try MeasureExpression property. It works BEFORE aggregations.

https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/configure-measure-properties?view=sql-server-2017

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ba16255-55b8-411c-a6ff-027701156f2c/ssas-measureexpression-influence-on-usage-of-aggregations?forum=sqlanalysisservices

https://blog.crossjoin.co.uk/2005/06/30/measure-expressions/

Answer 2

Try replaceing the first two code pieces with below. I have supposed that "AccountClassKey" is actually a dimension attribute, that you have made a measure just for this calculation. Based on that I reffered it as "[DimAccount].[AccountClass].[Account Class Key]"

CREATE MEMBER CURRENTCUBE.[Measures].[AccountClassKeyMultipiler] AS
(
CASE 
WHEN [DimAccount].[AccountClass].[Account Class Key].currentmember.Properties ("Member_Value",TYPED) = "1" THEN 1  
WHEN [DimAccount].[AccountClass].[Account Class Key].currentmember.Properties ("Member_Value",TYPED) = "2" THEN -1  
else 0
END);

CREATE MEMBER CURRENTCUBE.[Measures].[AvgBalMult] AS
[Measures].[Avg Bal] * [Measures].[AccountClassKeyMultipiler]

Edit Here I have tried to solve a similar case in Adventure works

We list the products and months with their sales

select 
{[Measures].[Internet Sales Amount]
}
on columns,
(
[Product].[Category].[Category],
[Date].[Month of Year].[Month of Year]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Next we convert the above query to give Average sales for each product based on months

with member 
[Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 
select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
}
on columns,
(
[Product].[Category].[Category]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Now we add a case based multiplier.Take a look at the last column

with
member [Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 

member [Measures].[Multipiler]
as
CASE 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Accessory' THEN 1  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Mountain' THEN 2  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Road' THEN 3  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Touring' THEN 4 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Components' THEN 5  
else 0
END

member [Measures].[Internet Sales AmountAvg2]
as
[Measures].[Internet Sales AmountAvg]*[Measures].[Multipiler]

select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
,
[Measures].[Multipiler]
,
[Measures].[Internet Sales AmountAvg2]
}
on columns,
(
[Product].[Category].[Category],[Product].[Product Line].[Product Line]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Now lets see what happens if we remove [Product].[Product Line].[Product Line] from rows

with
member [Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 

member [Measures].[Multipiler]
as
CASE 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Accessory' THEN 1  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Mountain' THEN 2  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Road' THEN 3  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Touring' THEN 4 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Components' THEN 5  
else 0
END

member [Measures].[Internet Sales AmountAvg2]
as
[Measures].[Internet Sales AmountAvg]*[Measures].[Multipiler]

select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
,
[Measures].[Multipiler]
,
[Measures].[Internet Sales AmountAvg2]
}
on columns,
(
[Product].[Category].[Category]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Bottom line is that in such cases where a measure's behaviour is dependant on a dimension attribute value, you need to have that dimension attribute in one of the axis. When it is not present default value is used. If the default value is not handled in the logic you end up with wrong results