CODEWITHSUNDEEP
ssasmdxolapbids
pirmas naujas
pirmas naujas

Reputation: 300

ssas total calculation incorrect when using IIF in calculated member

I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?

Calculated Member:

 CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
    AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
 VISIBLE = 1;

enter image description here

It seems that Total is calculated without checking value in "AverageScore" for that month.

thanks

Upvotes: 0

Views: 788

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

If avgscore is a hidden measure you don't want visible and if it's a physical measure not a calculated measure try adding this before your current calc:

Scope([Driver Dim].[Driver].[Driver].Members, [Date].[Month].[Month].Members);
  [Measures].[avgscore] = iif([Measures].[Distance]<2001,Null,[Measures].[avgscore]);
End scope;

That should zero out avgscore for low distance driver months so that the grand total will work right. If that won't work then explain avgscore further.

How many rows per driver per month are there? I assume more than one otherwise I would tell you to do the <2001 check in SQL.

Upvotes: 1

Related Questions