CODEWITHSUNDEEP
ssasmdxolapolap-cube
Luka Otočan
Luka Otočan

Reputation: 45

Total Sum of Calculated member is wrong

I have there tables:

Data model

Potrosnja is my Measure table and Artikal is Dimension. Grupa contains also a parent-child relationship between groups. For now, is only one level. Inside the Artikal dimension I created

Hierarchy: Grupa.ParentId, Grupa.GrupaId, Artikal.Id. Name of Hierarhcy is PGA.

This shows well in Power BI. I also create some calculated Members: Cijena LY and Vrijednost LY.

PowerBi Table

I need to create a calculated member and the formula goes

(Cijena - Cijena LY)* Kolicina

First I tried to create like a calculated member but I saw that my total was also creating like the formula of totals of formulas. I want that my total for this member is SUM of calculated member column

I trying to do this with SCOPE, but I don't get the right result. Here is my script:

CREATE MEMBER CURRENTCUBE.[Measures].[Suma razlike] AS null,
VISIBLE = 1;  

SCOPE([Measures].[Suma razlike],[Artikal].[PGA].members);
This =sum(
    Descendants([Artikal].[PGA].CurrentMember,,LEAVES),
    (([Measures].[Cijena]-[Measures].[Cijena LY])*[Measures].[Kolicina]));
END SCOPE;

Can somebody tell me what I am doing wrong? I creating this in Analysis services by Microsoft.

Upvotes: 1

Views: 579

Answers (1)

Luka Otočan
Luka Otočan

Reputation: 45

The solution was provided on the different blog by Alexei Stoyanovsky:

The problem was that was created hidden subselect. I needed to create a Dynamic set of Artikal. Here is solution:

CREATE DYNAMIC SET CURRENTCUBE.[MyTestSet] AS  [Artikal].[Id].[Id].members;    
SCOPE([Measures].[Suma razlike], [Artikal].[Id].members);    --Includes All + 
other members
This = case when [Artikal].[Id].currentmember.level.ordinal>0 then
([Measures].[Cijena]-[Measures].[Cijena LY])*[Measures].[Kolicina]
else
sum(existing [MyTestSet], ([Measures].[Cijena]-[Measures].[Cijena LY])* 
[Measures].[Kolicina]) --All member is not included
end;

FORMAT_STRING([Measures].[Vrijednost - CPI])='Percent'; 
FORMAT_STRING([Measures].[Postotak razlike])='Percent'; 
FORMAT_STRING([Measures].[Razlika artikal - CPI])='Percent'; 
END SCOPE;

Upvotes: 1

Related Questions