Search code examples
ssasmdx

How to find sum of each group in Hierarchy in MDX


In the example below I want to find sum of each group. Can someone correct my MDX query to get the desired result for GroupCount Column.

with member [GroupCount] as
aggregate([Department].[Department ID].currentmember.parent,
          [Measures].[Pay Frequency])


select {[Department].[Group Name].[all].children*
        [Department].[Department ID].[all].children
       } on rows,
{[Measures].[Pay Frequency],[GroupCount]} on columns
from test

The result I am getting from the above query is:

enter image description here

However I need the output as :

enter image description here


Solution

  • Is it like this?

    with member [GroupCount] as
    (
         [Department].[Group Name].CURRENTMEMBER,
         [Department].[Department ID].[All],
         [Measures].[Pay Frequency]
    )
    select {[Department].[Group Name].[all].children*
            [Department].[Department ID].[all].children
           } on rows,
    {[Measures].[Pay Frequency],[GroupCount]} on  columns
    from test;