This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have there tables:
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.
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.
Are you really going with MDX calculations for Tabular, or do you have an MD cube on top of those tables?
II have a Cube on top of those table. Model is Multidimensional. Also I have Time dimension.
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]))
Nope, this solution is not working. I will update the question with my Cube structure. Can you help me now or? Do you need more information about the cube?
I could have guessed the name of the Artikal ID attribute wrong, but this is the only possible problem with this approach I can currently see. How exactly is this solution not working?
As to more info about the cube, hopefully if there were any other calculations affecting the Suma razlike measure, you'd have mentioned them :)
Grand total for this column is wrong. 
As You can see Ukupno, or Total in English is 23792932,58. This is wrong. I export this view in Excel and tried SUM function on this column. The given value is -726186,8119 which is expected value. I don't get why is this total wrong? Id column is all Artikl's in database.
Hi @Luka Otočan , 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.
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?
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/
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:
You left out the EXISTING keyword before MyTestSet in the SUM. It was there for a reason, namely to make the measure react to slicing of the Artikal dimension. It is quite improbable that adding another calculated MDX measure to a visual should result in the visual no longer showing. And, if you absolutely must have a scoped assignment here, at least do it the useful way as described in the above blog post.
Yes, this works well, thank you :D. Now is showing well on all levels. I have a problem with showing Artikal Name. The loading of the Figure when I add Artikal name need about 5-10 Minutes. The problem is that Artikal Name is not unique in Artikal table. I have the same Artikal Name on different Artikal's but with unique ArtikalId.
This was made because we have the same Artikal Name from a different distributor. I don't want to calculate Measure like Suma Razlike on Sum of these Artikal's but on every Artikal by different Distributor separately. Do you know maybe how can I this solve without updating the source?
For speeding up the calculation, you could implement the solution from that blog post, and also perhaps replace the ID as the basis of the calculation with some less detailed attribute if this is acceptable for the analyses required.
If I understand correctly and the ID that is used for the calculation is the key of the dimension, this calculation already works separately. To visually distinguish the same artikal name from different distributors you could either slice your reports by distributor and artikal name, or craft a single attribute that will be differentiating enough. E.g. ID for attribute key and distributor name + artikal name for attribute name.