# question

## Total Sum of Calculated member is wrong

I have there tables:

• Artikal (eng. Item)

• Grupa (eng. Group)

• Potrosnja (eng. Consumption)

![Data model][1]

Based on this tables I created OLAP Cube. I have Measure Potrosnja and two dimensions: Artikal and Time. Artikal dimension is joined with Grupa table so we can create

Hierarchy:

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][2]

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.

· 7

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Are you really going with MDX calculations for Tabular, or do you have an MD cube on top of those tables?

AlexeiStoyanovsky

II have a Cube on top of those table. Model is Multidimensional. Also I have Time dimension.

LukaOtoan-8620

Then you will have to rewrite your question, describing the cube and not its source. Or perhaps you're lucky and your answer is as simple as sum(existing [Artikal].[Artikalid].[Artikalid], ([Measures].[Cijena]-[Measures].[Cijena LY])*[Measures].[Kolicina]))

Hi @LukaOtoan-8620, good to know that your issues have been resolved. Could you please do "Accept Answer" for this thread? By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

Smells like a hidden subselect. Declare a dynamic set of
[Artikal].[Artikalid].[Artikalid] and use it in the measure instead.

· 6

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, this works well. But this work only on 0 level. How to use this dynamic set on my Hierarchy:

[Artikal].[ParentId] -> [Artikal].[GroupId] -> [Artikal].[Id]?

``````  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 I use this Code or do I need another solution?

LukaOtoan-8620

This is in fact a universal solution. It's based on the most detailed attribute known to cover any possible drilldowns of the Artikal dimension. Try adding the measure to the matrix in your Figure 2 and see how it works for other levels of your user hierarchy.
I see no need to complicate things with a scoped assignment to a null calculated measure. You could use scoped assignment to a physical measure to speed things up as described in https://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

AlexeiStoyanovsky

If I dont use scope, my data wont show. My matrix in Figure 2 wont show:

With this code works:

`````` 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([MyTestSet], ([Measures].[Cijena]-[Measures].[Cijena LY])*[Measures].[Kolicina]) --All member is not included
end;
END SCOPE;
``````

But with this code works only zero level

When I put on 1 level i get Sum result on all groups: