Total Sum of Calculated member is wrong

Luka Otočan 21 Reputation points
2021-09-13T09:34:32.61+00:00

I have there tables:

  • Artikal (eng. Item)
  • Grupa (eng. Group)
  • Potrosnja (eng. Consumption)

Data model

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

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
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-09-13T15:05:20.493+00:00

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


0 additional answers

Sort by: Most helpful