CODEWITHSUNDEEP
sql-serverssasmdx
John Doe
John Doe

Reputation: 319

SSAS MDX Query with Grouped Value

I created Calculation Member which one shows a percentage ratio transactions amount to concrete institution.

 Case When IsEmpty ( [Measures].[Zlicz transakcje] ) 
 Then Null
 Else ROUND((( [Klient].[Instytucja].[Klient].CurrentMember,
   [Measures].[Zlicz transakcje] ) 
  /
  ( Root    ( [Klient] ), [Measures].[Zlicz transakcje] )) *100, 2 )
 End

The result of my query looks like:

enter image description here

Query: How to change MDX query to shows percentage usage for all institutions (except for "Brak instytucji") grouped in one rows (I mean one row with 13,05 value)?

Upvotes: 0

Views: 55

Answers (1)

whytheq
whytheq

Reputation: 35557

Couple of things I'd recommend

  • not use CASE - general rul-of-thumb is that IIF is quicker.
  • for readability I prefer the DIVIDE function.
  • rather than Root why not just use the ALL member?

Code would now be something like this:

 IIF(
   [Measures].[Zlicz transakcje] = 0 
  , NULL
  , ROUND(
      100 * 
      DIVIDE(
        ( [Klient].[Instytucja].[Klient].CurrentMember, [Measures].[Zlicz transakcje] ) 
      , ( [Klient].[Instytucja].[All], [Measures].[Zlicz transakcje] )
      )
      , 2 
    )

You can create an aggregated member that excludes Brak instytucji:

AGGREGATE(
  EXCEPT(
     [Klient].[Instytucja].[Klient].MEMBERS
    ,[Klient].[Instytucja].[Klient].[Brak instytucji]
  )
)

)

Upvotes: 1

Related Questions