question

LukaOtoan-8620 avatar image
0 Votes"
LukaOtoan-8620 asked CarrinWu-MSFT edited

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.

sql-server-analysis-services
· 7
5 |1600 characters needed characters left characters exceeded

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?

0 Votes 0 ·
LukaOtoan-8620 avatar image LukaOtoan-8620 AlexeiStoyanovsky ·

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

0 Votes 0 ·

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]))

0 Votes 0 ·
Show more comments

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!

0 Votes 0 ·

1 Answer

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered AlexeiStoyanovsky commented

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

· 6
5 |1600 characters needed characters left characters exceeded

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?


0 Votes 0 ·

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/

0 Votes 0 ·
LukaOtoan-8620 avatar image LukaOtoan-8620 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

131881-image.png

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

131883-image.png




0 Votes 0 ·
image.png (49.5 KiB)
image.png (51.6 KiB)
Show more comments