CODEWITHSUNDEEP
ssasmdx
arunbabu
arunbabu

Reputation: 289

How to get the Percentage using MDX

I want to find the percentage of claims in run time.
I learn that it can be achieved using calculated measure, but I do not know how to do that. Please guide me how to find the percentage of claims((line count/grandtotoal) *100).

enter image description here

Upvotes: 0

Views: 126

Answers (2)

whytheq
whytheq

Reputation: 35557

Try something like the following:

WITH 
   MEMBER [Measures].[PercentageOfAll] AS
       [Tmp Claim Dim].[Claimno].CURRENTMEMBER
       /
      ([Tmp Claim Dim].[Claimno].[All])
   ,FORMAT_STRING = 'Percent';
SELECT 
    {[Measures].[Tmp Claim Dim Count],
     [Measures].[PercentageOfAll]} ON 0,
     [Tmp Claim Dim].[Claimno].MEMBERS ON 1 
FROM [ACOE PI];

If you want the above to just apply to the measure [Tmp Claim Dim Count] then you could use tuples with this measure in your new measure:

WITH 
   MEMBER [Measures].[PercentageOfAll_ClaimCnt] AS
      ([Tmp Claim Dim].[Claimno].CURRENTMEMBER, [Measures].[Tmp Claim Dim Count])
       /
      ([Tmp Claim Dim].[Claimno].[All], [Measures].[Tmp Claim Dim Count]) 
   ,FORMAT_STRING = 'Percent';
SELECT 
    {[Measures].[Tmp Claim Dim Count]} ON 0,
     [Tmp Claim Dim].[Claimno].MEMBERS ON 1 
FROM [ACOE PI];

Upvotes: 1

Polux2
Polux2

Reputation: 592

You can adapt and try this :

WITH 
   MEMBER [Measures].[Percentage] AS
   [Measures].[line count]/
   ([Measures].[grandtotoal]), 
   FORMAT_STRING = 'Percent';
SELECT 
{[Measures].[Percentage]} ON COLUMNS, 
FROM [yourcube]

Upvotes: 0

Related Questions